我有以下SQL查询
SELECT
cs.group,
cs.code,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 1 THEN cs.amount ELSE 0 END),0) as a_jan,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 2 THEN cs.amount ELSE 0 END),0) as a_feb,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 3 THEN cs.amount ELSE 0 END),0) as a_mar,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 4 THEN cs.amount ELSE 0 END),0) as a_abr,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 5 THEN cs.amount ELSE 0 END),0) as a_may,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 6 THEN cs.amount ELSE 0 END),0) as a_jun,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 7 THEN cs.amount ELSE 0 END),0) as a_jul,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 8 THEN cs.amount ELSE 0 END),0) as a_ago,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 9 THEN cs.amount ELSE 0 END),0) as a_sep,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 10 THEN cs.amount ELSE 0 END),0) as a_oct,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 11 THEN cs.amount ELSE 0 END),0) as a_nov,
ISNULL(SUM(CASE WHEN datepart(mm,cs.scheduledate) = 12 THEN cs.amount ELSE 0 END),0) as a_dic,
ISNULL(SUM(cs.amount),0) as a_cds
FROM
employee_statement cs
WHERE
datepart(yyyy,cs.startdate) = 2018
GROUP BY
cs.group,
cs.code
此查询返回如下所示的表:
组是一个动态值,我可以对其进行硬编码,因为它不会改变太多,而代码是一个固定值列表。
所以我需要的是,无论特定的GROUP
或CODE
是否没有行,我都会得到值为0的行。
示例:
有什么线索吗?谢谢
发布于 2018-07-23 05:16:05
使用CROSS JOIN
生成行,使用LEFT JOIN
和GROUP BY
获得所需的结果:
查询的结构如下所示:
select g.group, c.code, . . .
from (select distinct group from employee_statement) g cross join
(select distinct code from employee_statement) c left join
employee_statement cs
on cs.group = g.group and cs.code = c.code and
cs.startdate >= '2018-01-01' and cs.startdate < '2019-01-01'
group by g.group, c.code;
请注意,我将where
子句更改为日期比较。使用函数通常意味着优化器不会使用索引。
另一个音符。我将计算得到的列如下:
SUM(CASE WHEN month(cs.scheduledate) = 1 THEN cs.amount ELSE 0 END) as a_jan,
也就是说,既不需要ISNULL()
,也不需要COALESCE()
(我的首选版本),因为您有ELSE 0
。我认为MONTH()
比DATEPART()
更容易理解。
https://stackoverflow.com/questions/51469297
复制相似问题