指望甲骨文

Question

我们在甲骨文数据库里有下面的数据-

col1 col2 
Z1     A
Z1     B
Z2     A
Z2     C
Z3     A   
Z4     D

我希望第二栏能-

出击-

col2  count
A      3     (Z1,Z2,Z3)
B      0     (Dont count if A is already present for record)
C      0
D      1      (Z4)

诚挚的问候

Answer 1

谢谢各位。但我可以这么做-

select  count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) like '%A%' then 1
            else null
        end) A,
        count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'B' then 1
            else null
        end) B,
        count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'C' then 1
            else null
        end)  C,
         count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'D' then 1
            else null
        end) D
from T
GROUP BY col1

谢谢你的答复

Answer 2

您可以使用窗口函数rank()来实现这一点。

select col2, count(case when rn = 1 then 1 end) cnt from (
select t.*,
    rank() over (partition by col1 order by case when col2 = 'A' then 1 else 2 end) rn
from table t
) group by col2;

Answer 3

对于您的命题，最常用的解决方案是，每个键COL1只按COL2键的第一次出现(按字母顺序排列)计算。

WITH tab AS
(
    SELECT 'Z1' col1, 'A' col2 FROM dual UNION ALL
    SELECT 'Z1' col1, 'B' col2 FROM dual UNION ALL
    SELECT 'Z2' col1, 'A' col2 FROM dual UNION ALL
    SELECT 'Z2' col1, 'C' col2 FROM dual UNION ALL
    SELECT 'Z3' col1, 'A' col2 FROM dual UNION ALL
    SELECT 'Z4' col1, 'D' col2 FROM dual 
), tab2 as (
select COL1, COL2,
row_number() over (partition by COL1 order by COL2) as rn
from tab)
select COL1, COL2,
case when rn = 1 then 1 else 0 end is_valid
from tab2
order by 1,2
;

COL1 COL2   IS_VALID
---- ---- ----------
Z1   A             1 
Z1   B             0 
Z2   A             1 
Z2   C             0 
Z3   A             1 
Z4   D             1 

其余的是简单的组，在IS_VALID上有一个和

select COL2, sum(is_valid) cnt  from tab3 -- TAB3 is the above row source
group by COL2
order by 1

COL2        CNT
---- ----------
A             3 
B             0 
C             0 
D             1