开发者社区> 问答> 正文

查询显示0而不显示

以下查询显示我想要的结果,除了我希望它在非生产情况下每个月显示0。

SELECT DATENAME(MONTH, DATEADD(M, MONTH(PolicyDetails.IssuedDate), - 1)) AS Month, SUM(PolicyDetails.Premium) AS TotalProduction, DATENAME(YEAR, PolicyDetails.IssuedDate) AS Year FROM PolicyDetails INNER JOIN Clients ON PolicyDetails.ClientId = Clients.ClientId WHERE (Clients.Username = @Username) GROUP BY MONTH(PolicyDetails.IssuedDate), DATENAME(YEAR, PolicyDetails.IssuedDate)

Month Total Production -$$ 2019 - August 30.00 2019 - October 45.00 在此表中,我想显示“ 2019年-9月”,其总生产= 0,而不显示任何内容。怎么样 ??

问题来源于stack overflow

展开
收起
保持可爱mmm 2019-11-18 11:36:11 449 0
1 条回答
写回答
取消 提交回答
  • 如果要显示数据中的所有月份,则可能最简单的方法是使用条件聚合。您对本月的计算似乎很尴尬。您似乎想要上个月,所以:

    SELECT DATENAME(YEAR, pd.IssuedDate) AS Year, DATENAME(MONTH, DATEADD(MONTH, -1, pd.IssuedDate)) AS Month, SUM(CASE WHEN c.Username = @Username THEN pd.Premium ELSE 0 END) AS TotalProduction
    FROM PolicyDetails pd INNER JOIN Clients c ON pd.ClientId = c.ClientId GROUP BY DATENAME(YEAR, pd.IssuedDate), DATENAME(MONTH, DATEADD(MONTH, -1, pd.IssuedDate)) ORDER BY MIN(pd.IssuedDate) 假设您每个月至少有一行数据。

    否则,规范的方法是生成所需的月份(使用派生表或递归CTE或日历表)。您的月份算术对于该解决方案来说有点尴尬。它看起来像:

    SELECT YEAR(DATEADD(MONTH, -1, months.mstart)), MONTH(DATEADD(MONTH, -1, months.mstart)), COALESCE(SUM(pd.Premium), 0) AS TotalProduction
    FROM (VALUES (CONVERT(DATE, '2019-08-01')), (CONVERT(DATE, '2019-09-01')), (CONVERT(DATE, '2019-10-01')) ) months(mstart) LEFT JOIN PolicyDetails pd ON pd.IssuedDate >= DATEADD(month, -1, months.mstart) AND pd.IssuedDate < months.mstart LEFT JOIN Clients c ON pd.ClientId = c.ClientId AND c.Username = @Username GROUP BY YEAR(DATEADD(MONTH, -1, months.mstart)), MONTH(DATEADD(MONTH, -1, months.mstart)) ORDER BY MIN(pd.IssuedDate)

    2019-11-18 11:36:21
    赞同 展开评论 打赏
问答地址:
问答排行榜
最热
最新

相关电子书

更多
低代码开发师(初级)实战教程 立即下载
冬季实战营第三期:MySQL数据库进阶实战 立即下载
阿里巴巴DevOps 最佳实践手册 立即下载