作者cutecpu (可爱中央处理器)
看板Database
标题Re: [SQL ] 合并资料表
时间Thu Oct 20 16:53:23 2011
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