iT邦幫忙

1

請問SELECT如何結果合併??

請問大大們,我要如何將以下收尋結果合併而不會相加在一起,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
做合併,最後資料顯是希望如下
https://ithelp.ithome.com.tw/upload/images/20190719/20119162ZiDZnCyOzH.png

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

2 個回答

4
fuzzylee1688
iT邦新手 2 級 ‧ 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
2
japhenchen
iT邦新手 3 級 ‧ 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
小魚 iT邦高手 1 級 ‧ 2019-07-19 10:25:29 檢舉

其實q1t那些應該不需要,
我也是這樣子想的,
只是不知道GROUP BY會不會有影響...

我怕遇上模棱兩可的錯誤,所以習慣會加上表別名

我們的想法比較適合作跨表使用,不過,簡單的方式反倒比較能理解

至於GROUP BY影不影響,我常在用,主表的GROUP跟子表之間不會互相影響......

我要發表回答

立即登入回答