如何使用子查询每月显示查询-编程知识-白鹭情

我在这里有一个带有子查询的查询。这向我展示了今年的完整结果。我如何让它每月显示给我。我尝试了几件事，但总是收到错误讯息

这是我的查询

SELECT 
    masch_nr, SUM(dauer) AS Prozess_Verfügbarkeit,
    (SELECT SUM(dauer)
     FROM [hydra1].[hydadm].[ereignis]
     WHERE YEAR(begin_ts) = YEAR(CURRENT_TIMESTAMP)
       AND masch_nr = 'FIMI1'
       AND bmktonr IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11')) AS Verfügbarkeit
FROM 
    [hydra1].[hydadm].[ereignis]
WHERE 
    YEAR(begin_ts) = YEAR(CURRENT_TIMESTAMP)
    AND masch_nr = 'FIMI1'
    AND bmktonr IN ('7', '11')
GROUP BY 
    masch_nr

结果应如下所示：

Month | Prozess_Verfügbarkeit | Verfügbarkeit
------ ----------------------- --------------
 1    |         344           |     4556
 2    |         445           |     5654

谢谢

uj5u.com热心网友回复：

您可以通过使用条件聚合来简化这一点

SELECT 
  YEAR(begin_ts) AS [Year] 
, MONTH(begin_ts) AS [Month] 
, masch_nr
, SUM(CASE WHEN bmktonr IN ('7', '11') 
      THEN dauer END) AS Prozess_Verfügbarkeit
, SUM(CASE WHEN bmktonr IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11') 
      THEN dauer END) AS Verfügbarkeit
FROM [hydra1].[hydadm].[ereignis]
WHERE masch_nr = 'FIMI1'
  AND begin_ts >= DATEADD(YEAR,DATEDIFF(YEAR,0,CURRENT_TIMESTAMP),0)
  AND begin_ts <  DATEADD(YEAR,DATEDIFF(YEAR,0,CURRENT_TIMESTAMP) 1,0)
GROUP BY YEAR(begin_ts), MONTH(begin_ts), masch_nr
ORDER BY [Year], [Month], masch_nr