我在這里有一個帶有子查詢的查詢。這向我展示了今年的完整結果。我如何讓它每月顯示給我。我嘗試了幾件事，但總是收到錯誤訊息
這是我的查詢
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/407869.html
標籤：
上一篇：如何每月合并2個不同的查詢