这个sql该如何写

2012-04-26

这个sql该怎么写表TuseroperatetimeLiMingLogin2010-10-24 08:03:00WangYiLogin2010-10-24 08:14:00WangYi

这个sql该怎么写
表T
user operate time
LiMingLogin2010-10-24 08:03:00
WangYiLogin2010-10-24 08:14:00
WangYiLogout2010-10-24 16:14:00
LiMingLogout2010-10-24 16:44:00

写个sql语句变为
LiMing 8:13
WangYi 8:46
该怎么写呢

[解决办法]

SQL code

declare @T table ([user] varchar(6),operate varchar(6),time datetime)insert into @Tselect 'LiMing','Login','2010-10-24 08:03:00' union allselect 'WangYi','Login','2010-10-24 08:14:00' union allselect 'WangYi','Logout','2010-10-24 16:14:00' union allselect 'LiMing','Logout','2010-10-24 16:44:00';with maco as(select [user],datediff(mi,max(case operate when 'Login' then [time] else '' end),max(case operate when 'Logout' then [time] else '' end)) as mifrom @T group by [user])select [user],ltrim(mi/60)+'小时'+ltrim(mi%60)+'分' as 时长 from maco/*user   时长------ ------------------------------LiMing 8小时41分WangYi 8小时0分*/
[解决办法]
SQL codeDROP TABLE tb0CREATE TABLE tb0(    user1 VARCHAR(10),    operate VARCHAR(10),    time DATETIME)GOINSERT INTO tb0SELECT 'LiMing', 'Login', '2010-10-24 08:03:00' UNIONSELECT 'WangYi', 'Login', '2010-10-24 08:14:00' UNIONSELECT 'WangYi', 'Logout', '2010-10-24 16:14:00' UNIONSELECT 'LiMing', 'Logout', '2010-10-24 16:44:00'SELECT User1,RIGHT(CONVERT(VARCHAR(100),(MAX(time) - MIN(time)),120),8)FROM tb0GROUP BY user1,LEFT(time,10)User1    (No column name)LiMing    08:41:00WangYi    08:00:00

热点排行

Bad Request.

SQL Server

这个sql该如何写