22,207
社区成员
发帖
与我相关
我的任务
分享
create table #ta(id int,name varchar(10), upname varchar(100))
insert into #ta
select 111,'AAA',null
union all select 222,'BBB','AAA'
union all select 333,'CCC','AAA'
union all select 444,'DDD',null
create table #tb(id int, onlineTime int)
insert into #tb
select 111,2323
union all select 111,2323
union all select 222,5656
union all select 222,5656
union all select 333,782
union all select 333,656
union all select 444,569
union all select 444,856
select b.upname,a.id,count(b.id) 总下线人数,
count(case when m.onlinetime > 5000 then m.id end) 上线人数
from #ta b
left join #ta a on b.upname = a.name
left join (select id,SUM(onlinetime) as onlinetime from #tb group by id) m on b.id = m.id
where b.upname is not null
group by b.upname,a.id
drop table #ta,#tb
/*
upname id 总下线人数 上线人数
AAA 111 2 1
*/
select b.upname,a.id,count(b.id) 总下线人数,
count(case when m.onlinetime > 5000 then m.id end)
from tableA b
left join tableA a on b.upname = a.name
left join (select id,SUM(onlinetime) as onlinetime from tableB group by id) m on b.id = m.id
where b.upname is not null
group by b.upname,a.id