按存储桶计数- plsql

Question

假设我有下表：

ID  Apple Orange Banana
1    Y     Y      N
2    N     Y      N
3    Y     N      N
4    Y     N      Y

我应该如何在plsql中编写查询，这将给我带来以下结果？

ID  Apple Orange Banana
Y    3     2      1
N    1     2      3

Answer 1

下面是一个使用条件聚合的方法：

select x.id,
       sum(case when apple = x.id then 1 else 0 end) as Apple,
       sum(case when orange = x.id then 1 else 0 end) as Orange,
       sum(case when banana = x.id then 1 else 0 end) as Banana
from t cross join
     (select 'Y' as id from dual union all
      select 'N' from dual
     ) x
group by x.id;

Answer 2

SELECT 'Y' id,
  SUM(DECODE(apple,'Y',1)) apple_cnt,
  SUM(DECODE(Orange,'Y',1)) Orange_cnt,
  SUM(DECODE(Banana,'Y',1)) Banana_cnt
FROM
  (SELECT '1' ID, 'Y' Apple, 'Y' Orange, 'N' Banana FROM dual
  UNION ALL
  SELECT '2' , 'N', 'Y', 'N' FROM dual
  UNION ALL
  SELECT '3' , 'Y' , 'N' , 'N' FROM dual
  UNION ALL
  SELECT '4' , 'Y' , 'N' , 'Y' FROM dual
  )
UNION ALL
SELECT 'N' id,
  SUM(DECODE(apple,'N',1)) apple_cnt,
  SUM(DECODE(Orange,'N',1)) Orange_cnt,
  SUM(DECODE(Banana,'N',1)) Banana_cnt
FROM
  (SELECT '1' ID, 'Y' Apple, 'Y' Orange, 'N' Banana FROM dual
  UNION ALL
  SELECT '2' , 'N', 'Y', 'N' FROM dual
  UNION ALL
  SELECT '3' , 'Y' , 'N' , 'N' FROM dual
  UNION ALL
  SELECT '4' , 'Y' , 'N' , 'Y' FROM dual
  )

Answer 3

如果表的结构不是必须的，我建议你这样结构：

ID  FRUIT   FLAG
1   Apple   Y
2   Apple   Y
3   Apple   Y
4   Apple   N
5   Orange  N
6   Orange  N
7   Orange  N
8   Orange  Y
9   Orange  Y
10  Banana  Y
11  Banana  N
12  Banana  Y
13  Banana  Y
14  Banana  N

对于此设计，您可以使用Pivot Query

SELECT *
  FROM (SELECT Fruit, Flag FROM tab1) PIVOT (COUNT (Fruit)
                                      FOR (fruit)
                                      IN  ('Apple' AS Apple,
                                          'Orange' AS Orange,
                                          'Banana' AS Banana))

结果：

FLAG    APPLE   ORANGE  BANANA
Y       3       2       3
N       1       3       2

另一种变体：

SELECT *
  FROM (SELECT Fruit, Flag FROM tab1) PIVOT (COUNT (Flag)
                                      FOR (Flag)
                                      IN ('Y' AS Yes, 'N' AS No))

结果：

FRUIT   YES NO
Apple   3   1
Orange  2   3
Banana  3   2