Oracle SQL在单列中计算不同值的实例 [英] Oracle sql to count instances of different values in single column

问题描述

我有一个带有状态列的表.我想要一个Oracle sql查询，该查询将仅在一行中列出每种状态下的行数. 例如，如果我的桌子是

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

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函数:

If you are using Oracle 11g, then you can use the PIVOT function:

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语句一起使用:

If you are not using Oracle11g, then you can use an aggregate function with a CASE statement:

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小提琴

这篇关于Oracle SQL在单列中计算不同值的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！