如何使用基于列值的group by?

kx1ctssn  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(395)

我试图使用组,但无法实现输出,我想组的日期,班次和质量。
数据如下:

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
w80xi6nr

w80xi6nr1#

你可以用 Conditional Aggregation 通过 GROUP 惯性导航与制导 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

演示

voj3qocg

voj3qocg2#

SQL> with t(mydate, shift, mass) as
  2  (
  3   select '01-05-2020', 'A', 5 from dual union all
  4   select '01-05-2020', 'B', 3 from dual union all
  5   select '01-05-2020', 'B', 3 from dual union all
  6   select '02-05-2020', 'A', 11 from dual union all
  7   select '02-05-2020', 'A', 5 from dual union all
  8   select '02-05-2020', 'C', 12 from dual union all
  9   select '02-05-2020', 'C', 12 from dual union all
 10   select '02-05-2020', 'B', 5 from dual
 11  )
 12  SELECT mydate,shift,
 13         SUM(CASE WHEN mass >=3 AND
 14                       mass < 10 THEN 1 ELSE 0 END) AS "mass>=3",
 15         SUM(CASE WHEN mass >10 THEN 1 ELSE 0 END) AS "mass>10"
 16    FROM t
 17   GROUP BY mydate,shift
 18   ORDER BY mydate,shift;

MYDATE     S    mass>=3    mass>10
---------- - ---------- ----------
01-05-2020 A          1      0
01-05-2020 B          2      0
02-05-2020 A          1      1
02-05-2020 B          1      0
02-05-2020 C          0      2

SQL> 
SQL>

相关问题