請問SELECT如何結果合併??

select

bluewind 2019-07-19 09:55:31 ‧ 1409 瀏覽

分享至

請問大大們，我要如何將以下收尋結果合併而不會相加在一起，UNION不適用，求解!?

SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q1' FROM cs.ScoreC where cMonth BETWEEN '01' AND '03' GROUP BY cCorpIdn,cVendorName

SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q2' FROM cs.ScoreC where cMonth BETWEEN '04' AND '05' GROUP BY cCorpIdn,cVendorName

SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q3' FROM cs.ScoreC where cMonth BETWEEN '07' AND '09' GROUP BY cCorpIdn,cVendorName

SELECT cCorpIdn,cVendorName,AVG(fraction)as 'Q4' FROM cs.ScoreC where cMonth BETWEEN '10' AND '11' GROUP BY cCorpIdn,cVendorName

合併後再與
SELECT
cCorpIdn,cVendorName,
[01] as '一月', [02] as '二月', [03] as '三月', [04] as '四月',
[05] as '五月', [06] as '六月', [07] as '七月', [08] as '八月',
[09] as '九月', [10] as '十月', [11] as '十一月', [12] as '十二月'
FROM (
SELECT cCorpIdn,cVendorName,cYear, cMonth, fraction
FROM cs.ScoreC
GROUP BY cCorpIdn,cVendorName,cYear, cMonth, fraction
) as GroupTable
PIVOT
(
Sum(fraction)
FOR cMonth IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])
) AS PivotTable
做合併，最後資料顯是希望如下

slime iT邦大師 1 級 ‧ 2019-07-19 10:07:51 檢舉

先產出每個月的總計, 產生暫存表 M , 另外產出每季的總計, 產生暫存表 Q , 然後再一個 select 對暫存表 M 及暫存表 Q join .

大約是:
select a.* , b.q1 , b.q2 , b.q3 , b.q4 from
(select cCorpIdn , m1 , m2 , m3 , .... from cs.ScoreC) a ,
(select cCorpIdn , q1 , q2 , q3 , .... from cs.ScoreC) b
where a.cCorpIdn = b.cCorpIdn

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

fuzzylee1688

iT邦研究生 3 級 ‧ 2019-07-19 10:34:46

最佳解答

我覺得應該可以這麼寫.

SELECT	cCorpIdn,cVendorName
       ,SUM(CASE WHEN cMonth = '01' THEN fraction ELSE NULL END) as '一月'
	   ,SUM(CASE WHEN cMonth = '02' THEN fraction ELSE NULL END) as '二月'
	   ..
	   ..
	   ,AVG(CASE WHEN cMonth BETWEEN '01' AND '03' THEN fraction ELSE NULL END)as 'Q1' 
	   ,AVG(CASE WHEN cMonth BETWEEN '04' AND '06' THEN fraction ELSE NULL END)as 'Q2' 
	   ,AVG(CASE WHEN cMonth BETWEEN '07' AND '09' THEN fraction ELSE NULL END)as 'Q3' 
	   ,AVG(CASE WHEN cMonth BETWEEN '10' AND '12' THEN fraction ELSE NULL END)as 'Q4' 
FROM	cs.ScoreC 
where	cYear='2019'
GROUP BY cCorpIdn,cVendorName

回應
分享
檢舉

登入發表回應

japhenchen

iT邦超人 1 級 ‧ 2019-07-19 10:20:39

沒得測試，有錯誤的話請自行修正，用子表SELECT的結果做欄位即可

結果應該是
cCorpIdnA,cVendorNameA,Q1,Q2,Q3,Q4
cCorpIdnB,cVendorNameB,Q1,Q2,Q3,Q4
cCorpIdnC,cVendorNameC,Q1,Q2,Q3,Q4

SELECT 
    cCorpIdn,cVendorName,
    (SELECT ISNULL(AVG(fraction),0) 
     FROM cs.ScoreC as q1t
     where q1t.cMonth BETWEEN '01' AND '03'
     AND q1t.cCorpIdn=MT.cCorpIdn) AS 'Q1',
    (SELECT ISNULL(AVG(fraction),0) 
     FROM cs.ScoreC as q2t
     where q2t.cMonth BETWEEN '04' AND '06'
     AND q2t.cCorpIdn=MT.cCorpIdn) AS 'Q2',
    (SELECT ISNULL(AVG(fraction),0) 
     FROM cs.ScoreC as q3t
     where q3t.cMonth BETWEEN '07' AND '09'
     AND q3t.cCorpIdn=MT.cCorpIdn) AS 'Q3',
    (SELECT ISNULL(AVG(fraction),0) 
     FROM cs.ScoreC as q4t
     where q4t.cMonth BETWEEN '10' AND '12'
     AND q4t.cCorpIdn=MT.cCorpIdn) AS 'Q4',
FROM cs.ScoreC AS MT
GROUP BY cCorpIdn,cVendorName