如何每月合并2個不同的查詢

我有 2 個查詢，每月都會顯示結果。這些對我來說太完美了。現在我希望兩張表并排顯示。

SELECT 

MONTH(begin_ts) AS [Month]     
,SUM(CASE WHEN bmktonr = '7' 
THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
THEN dauer ELSE reserve1 END) 
AS Prozess_Verfügbarkeit

,SUM(CASE WHEN bmktonr = '1' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
  THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
  THEN dauer ELSE reserve1 END)
  AS Verfügbarkeit

FROM [hydra1].[hydadm].[v_ereignis]
WHERE masch_nr = 'FIMI1'
AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
GROUP BY MONTH(begin_ts)
ORDER BY [Year], [Month]

這個查詢

SELECT p.masch_nr
,SUM(b.ruest_zeit) as SOLLRüsten
,SUM(b.bearb_zeit) as SOLLProduktion
,SUM(b.ruest_zeit_zuschl) as SOLLZuschlag
,SUM(p.bmk_07) as ISTRüsten
,SUM(p.bmk_11) as ISTProduktion
,MONTH(prot_dat) as Month
FROM [hydra1].[hydadm].[v_auftrag_status] p
JOIN [hydra1].[hydadm].[v_auftrags_bestand] b
ON b.auftrag_nr = p.auftrag_nr
WHERE p.masch_nr = 'GEORG'
AND a_status = 'E'
AND YEAR(prot_dat)=YEAR(CURRENT_TIMESTAMP)

GROUP BY p.masch_nr, MONTH(prot_dat)

uj5u.com熱心網友回復：

您可以嘗試以下查詢來組合兩個資料集。

  SELECT * FROM 
  (
     SELECT 
     MONTH(begin_ts) AS [Month]     
     ,SUM(CASE WHEN bmktonr = '7' 
      THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
      THEN dauer ELSE reserve1 END) 
     AS Prozess_Verfügbarkeit

    ,SUM(CASE WHEN bmktonr = '1' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '2' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '3' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '4' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '5' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '6' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '7' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '8' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '9' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '10' 
     THEN dauer ELSE reserve1 END)   SUM(CASE WHEN bmktonr = '11' 
     THEN dauer ELSE reserve1 END)
     AS Verfügbarkeit

    FROM [hydra1].[hydadm].[v_ereignis]
    WHERE masch_nr = 'FIMI1'
    AND YEAR(begin_ts)=YEAR(CURRENT_TIMESTAMP)
    GROUP BY MONTH(begin_ts)
  )T
  INNER JOIN
  (
    SELECT p.masch_nr
    ,SUM(b.ruest_zeit) as SOLLRüsten
    ,SUM(b.bearb_zeit) as SOLLProduktion
    ,SUM(b.ruest_zeit_zuschl) as SOLLZuschlag
    ,SUM(p.bmk_07) as ISTRüsten
    ,SUM(p.bmk_11) as ISTProduktion
    ,MONTH(prot_dat) as Month
    FROM [hydra1].[hydadm].[v_auftrag_status] p
    JOIN [hydra1].[hydadm].[v_auftrags_bestand] b
    ON b.auftrag_nr = p.auftrag_nr
    WHERE p.masch_nr = 'GEORG'
    AND a_status = 'E'
    AND YEAR(prot_dat)=YEAR(CURRENT_TIMESTAMP)

    GROUP BY p.masch_nr, MONTH(prot_dat)
  )T1 on T.Month = T1.Month
  Order by T.Month 

標籤：

上一篇：使用MSTest的C#單元測驗DynamicDataDisplayName

下一篇：如何使用子查詢每月顯示查詢