您可以使用相关子查询来完成此操作,如下所示:
SELECT
Company,
Date,
(SELECT COUNT(*) FROM MyTable AS T1
WHERE T1.Flag='Y' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_Y,
(SELECT COUNT(*) FROM MyTable AS T1
WHERE T1.Flag='N' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_N
FROM MyTable AS T2
GROUP BY Company, Date
您也可以更简洁地做到这一点,但使用(可以说)可能会稍微降低可读性SUM
trick:
SELECT
Company,
Date,
SUM(CASE WHEN Flag='Y' THEN 1 ELSE 0 END) AS Count_Y,
SUM(CASE WHEN Flag='N' THEN 1 ELSE 0 END) AS Count_N,
FROM MyTable
GROUP BY Company, Date
在 Oracle/PLSQL 中,DECODE
函数可以用来替换CASE
为了更简洁:
SELECT
Company,
Date,
SUM(DECODE(Flag,'Y',1,0)) AS Count_Y,
SUM(DECODE(Flag,'N',1,0)) AS Count_N,
FROM MyTable
GROUP BY Company, Date