1

## 請問SELECT如何結果合併??

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 檢舉

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邦新手 1 級 ‧ 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邦高手 1 級 ‧ 2019-07-19 10:20:39

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
``````