34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb1(userName varchar(10))
insert into tb1 values('tom')
insert into tb1 values('cake')
insert into tb1 values('Mike')
create table tb2(userName varchar(10),beginTime datetime,endTime datetime)
insert into tb2 values('tom' ,'2010-11-2 8:00:00', '2010-11-3 12:00:00')
insert into tb2 values('tom' ,'2010-11-3 9:00:00', '2010-11-5 12:00:00')
insert into tb2 values('Mike' ,'2010-11-5 9:00:00', '2010-11-7 12:00:00')
go
--1
select m.userName , isnull(sum(datediff(mi,n.beginTime ,n.endTime)),0) from tb1 m
left join tb2 n on m.userName = n.userName
group by m.userName
/*
userName
---------- -----------
cake 0
Mike 3060
tom 4740
*/
--2
select m.* , n.* from tb1 m, tb2 n where m.userName = n.userName
and getdate() between n.beginTime and n.endTime
/*
userName userName beginTime endTime
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
(所影响的行数为 0 行)
*/
drop table tb1,tb2