表T
user operate time
LiMing Login 2010-10-24 08:03:00
WangYi Login 2010-10-24 08:14:00
WangYi Logout 2010-10-24 16:14:00
LiMing Logout 2010-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 code
DROP 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