有两张表:
表1
id name Permissions
1 aa 1
2 bb 2
3 cc 1
4 dd 1
5 ee 2
6 ff 1表2
id uid name state
1 1 aa 2
2 1 aa 2
3 1 aa 1
4 1 aa 2
5 3 cc 1
6 3 cc 1
7 3 cc 1
8 4 dd 2
9 4 dd 2想检索出的结果为
name num
aa 1
cc 3
dd 0
ff 0就是检索表1中Permissions为1的name,表2的uid=表1的id,然后检索出表2中对应的的state不为2的数量,没有的为0,这个该如何写?
表1
id name Permissions
1 aa 1
2 bb 2
3 cc 1
4 dd 1
5 ee 2
6 ff 1表2
id uid name state
1 1 aa 2
2 1 aa 2
3 1 aa 1
4 1 aa 2
5 3 cc 1
6 3 cc 1
7 3 cc 1
8 4 dd 2
9 4 dd 2想检索出的结果为
name num
aa 1
cc 3
dd 0
ff 0就是检索表1中Permissions为1的name,表2的uid=表1的id,然后检索出表2中对应的的state不为2的数量,没有的为0,这个该如何写?
select a.name,count(b.id) as num from 表1 as a left join 表2 as b on a.id=b.uid group by a.name having a.Permissions=1 and b.state<>2
SUM(case when b.state<>2 then 1 else 0 end)
FROM tab1 as a LEFT JION tab2 as ON a.id = b.uid
GROUP BY a.name你试试吧。
SUM(case when b.state <>2 then 1 else 0 end)
FROM tab1 as a LEFT JION tab2 as b ON a.id = b.uid
GROUP BY a.name
name num
aa 1
cc 3
SUM(case when b.state <>2 then 1 else 0 end)
FROM tab1 as a LEFT JION tab2 as ON a.id = b.uid
WHERE a.Permissions = 1
GROUP BY a.name
select [name],
num=(select count(case when state<>2 then 1 else null end) from @tb where uid=a.id)
from @ta a where Permissions=1
--Result
/*
name num
---- -----------
aa 1
cc 3
dd 0
ff 0
*/