Oracle sql 计算单列中不同值的实例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13238142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sql to count instances of different values in single column
提问by Thunderhashy
I have a table with status column. I want an Oracle sql query which will list me count of rows in each status in only one row. for eg if my table is
我有一个带有状态列的表。我想要一个 Oracle sql 查询，它将仅在一行中列出每个状态中的行数。例如，如果我的桌子是
Table A
Id Status Fkey
1 20 500
2 20 500
3 30 501
4 40 501
5 30 502
Output should be
输出应该是
Fkey Count_status20 Count_status30 Count_status40
500 2 0 0
501 0 1 1
A slight twist here
这里稍微有点扭曲
Table B
FKey TKey
500 1001
501 1001
502 1002
Now Output should be
现在输出应该是
TKey Count_status20 Count_status30 Count_status40
1001 2 1 1
1002 0 1 0
回答by Taryn
If you are using Oracle 11g, then you can use the PIVOTfunction:
如果您使用的是 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;
If you are not using Oracle11g, then you can use an aggregate function with a CASEstatement:
如果您没有使用 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
回答by juergen d
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
