我有一个带状态列的表。我想要一个Oracle SQL查询,它将仅在一行中列出每个状态中的行计数。例如,如果我的桌子是
Table A
Id Status Fkey
1 20 500
2 20 500
3 30 501
4 40 501
5 30 502
输出应为
Fkey Count_status20 Count_status30 Count_status40
500 2 0 0
501 0 1 1
这里有轻微的扭曲
Table B
FKey TKey
500 1001
501 1001
502 1002
现在输出应为
TKey Count_status20 Count_status30 Count_status40
1001 2 1 1
1002 0 1 0
如果您使用的是Oracle 11G,则可以使用PIVOT
函数:
select *
from
(
select tkey, status,
status as col
from tableB b
left join tableA a
on a.fkey = b.fkey
) src
pivot
(
count(status)
for col in ('20' as Count_Status20,
'30' as Count_Status30,
'40' as Count_Status40)
) piv;
请参阅带有演示的SQL小提琴
如果您不使用Oracle11g,则可以使用CASE
语句使用聚合函数:
select tkey,
count(case when status = 20 then 1 else null end) as Count_Status20,
count(case when status = 30 then 1 else null end) as Count_Status30,
count(case when status = 40 then 1 else null end) as Count_Status40
from tableB b
left join tableA a
on b.fkey = a.fkey
group by tkey
请参阅带有演示的SQL小提琴
select fkey,
sum(case when status = 20 then 1 else 0 end) as count_status20,
sum(case when status = 30 then 1 else 0 end) as count_status30,
sum(case when status = 40 then 1 else 0 end) as count_status40,
from your_table
group by fkey