34,838
社区成员




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