时间戳

首页 > 解决方案 > 查询只返回当前月份

sql-server - 查询只返回当前月份

问题描述

我使用 SQL Server，我希望有一个当前年份的表，其中显示今年的所有月份，即使值为 0。

结果显示给我：

     Month  Summe    maschnr   
    -------------------------
     1      300         23

这是我今年想要的示例：

     Month  Summe   maschnr.   
    ------------------------
     1      300       23
     2      0         23
     3      0         23
     4      0         23 
     5      0         23 
     6      0         23
     7      0         23
     8      0         23 
     9      0         23
     10     0         23
     11     0         23
     12     0         23

你能帮助我吗？

我的查询是

SELECT 
    masch_nr, 
    CONVERT(DECIMAL(10, 2), SUM(gut_pri) / 1000) AS summe, 
    MONTH(begin_ts) AS Monat
FROM 
    [hydra1].[hydadm].[v_ade_protokoll]
WHERE 
    masch_nr = 'FIMI3'
    AND satz_art = 'T'
    AND YEAR(begin_ts) = '2021'
GROUP BY 
    masch_nr, MONTH(begin_ts)

谢谢

标签： sql-server

解决方案

您需要为不存在的数据填补空白。你表中的哪一行可能输出二月？

DECLARE @year int = 2021;

;WITH n AS 
(
  SELECT m FROM (VALUES(1),(2),(3),(4)) AS m(m)
),
m(m) AS 
(
  SELECT TOP (12) DATEFROMPARTS(@year, ROW_NUMBER() OVER (ORDER BY n.m), 1) 
  FROM n CROSS JOIN n AS n2
)
SELECT [Month] = MONTH(m.m), 
  CONVERT(DECIMAL(10,2),SUM(ap.gut_pri)/1000) AS summe, 
  ap.masch_nr
FROM m 
LEFT OUTER JOIN hydra1.hydadm.v_ade_protokoll AS ap
  ON  ap.begin_ts >= m.m 
  AND ap.begin_ts < DATEADD(MONTH,1,m.m)
  AND ap.masch_nr = 'FIMI3' 
  AND ap satz_art = 'T'
GROUP BY m.m, 
  ap.masch_nr -- do you really need this? There can only be one
ORDER BY m.m;

推荐阅读