SELECT A.YEAR,A.COUNT,A.AMEA,B.COUNT,B.AMEA,C.COUNT,C.AMEA
FROM (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_A
GROUP BY YEAR
) A FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_B
GROUP BY YEAR
) B ON A.YEAR = B.YEAR FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_C
GROUP BY YEAR
) C ON C.YEAR = A.YEAR OR C.YEAR = B.YEAR
UNION
SELECT B.YEAR,A.COUNT,A.AMEA,B.COUNT,B.AMEA,C.COUNT,C.AMEA
FROM (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_A
GROUP BY YEAR
) A FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_B
GROUP BY YEAR
) B ON A.YEAR = B.YEAR FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_C
GROUP BY YEAR
) C ON C.YEAR = A.YEAR OR C.YEAR = B.YEAR
UNION
SELECT C.YEAR,A.COUNT,A.AMEA,B.COUNT,B.AMEA,C.COUNT,C.AMEA
FROM (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_A
GROUP BY YEAR
) A FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_B
GROUP BY YEAR
) B ON A.YEAR = B.YEAR FULL OUTER JOIN (
SELECT YEAR,COUNT(*) AS COUNT,SUM(MEA) AS AMEA
FROM TABLE_C
GROUP BY YEAR
) C ON C.YEAR = A.YEAR OR C.YEAR = B.YEAR
※ 引述《insert (我是好孩子)》之銘言：
: 很抱歉我本來是想將本來的問題簡單化後比較好詢問
: 發現是自作聰明了
: 重新改過 在麻煩各位幫我看一下 真的很抱歉
: 使用MSSQL
: 想將3個資料表合併為一個
: 3個資料表的欄位都相同 是由where條件不同而成
: Table A
: --------------
: year id mea
: 100 001 10
: 100 002 20
: 99 006 15
: 99 007 21
: 98 008 10
: Table B
: ---------------
: year id mea
: 99 003 10
: 99 004 20
: 99 005 15
: 98 009 23
: Table C
: ---------------
: year id mea
: 98 010 12
: 想合併計算下面這個表單
: count 為count(id) amea為SUM(mea)
: Table ABC
: ----------------------------------------
: A B C
: year count amea count amea count amea
: 100 2 30 - - - -
: 99 2 36 3 45 - -
: 98 1 10 1 23 1 12
: 是過union 但會將相同欄併為一個(count amea)
: join 因為3個表沒有關聯 所以...
: 麻煩了 謝謝
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 210.59.164.109