你可以用 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

演示

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>