sql - 如何根据列值使用 Group By？

Question

我正在尝试使用 Group By，但无法通过 Date、Shift 和 Mass 实现我想要分组的输出。

数据如下：

date     | shift | mass 
---------+-------+------
01-05-20 | A     | 5
01-05-20 | B     | 3
01-05-20 | B     | 3
02-05-20 | A     | 11
02-05-20 | A     | 5
02-05-20 | C     | 12
02-05-20 | C     | 12
02-05-20 | B     | 5

我想要的输出

date     | shift | mass>3 | mass>10 
---------+-------+--------+--------
01-05-20 | A     | 1      | 0
01-05-20 | B     | 2      | 0
02-05-20 | A     | 1      | 1
02-05-20 | B     | 1      | 0
02-05-20 | C     | 0      | 2

标签： sqloraclegroup-bycountpivot

Answer 1

您可以Conditional Aggregation通过GROUPing使用BY mydate,shift：

SELECT mydate,shift,
       SUM(CASE WHEN mass > 3 THEN 1 ELSE 0 END) AS "mass>3",
       SUM(CASE WHEN mass >10 THEN 1 ELSE 0 END) AS "mass>10"
  FROM t
 GROUP BY mydate,shift
 ORDER BY mydate,shift;

顺便说一句（正如您在评论中要求的那样），您还可以使用DECODE()函数：

SELECT mydate,shift,
       SUM(DECODE(SIGN(mass-3),1,1,0)) AS "mass>3",
       SUM(DECODE(SIGN(mass-10),1,1,0)) AS "mass>10"
  FROM t
 GROUP BY mydate,shift
 ORDER BY mydate,shift

Demo