三个表组合问题

[zhjs0505]

有三个表层数列不同，如何把这三个表组合到一个表中，并且分层数
大神，帮帮忙

[中国风]

SELECT 编码 
,MAX(CASE WHEN Tab=1 THEN 编码 END) AS 编码1
,MAX(CASE WHEN Tab=1 THEN 价格 END) AS 价格1
,MAX(CASE WHEN Tab=2 THEN 编码 END) AS 编码2
,MAX(CASE WHEN Tab=2 THEN 价格 END) AS 价格2
,MAX(CASE WHEN Tab=3 THEN 编码 END) AS 编码3
,MAX(CASE WHEN Tab=3 THEN 价格 END) AS 价格3
FROM 
(SELECT  层数,编码=编码1,价格=价格1,*,Tab=1 FROM    t1
UNION ALL
SELECT  *,Tab=2 FROM    t2
UNION ALL
SELECT  *,Tab=3 FROM    t3
) AS a
GROUP BY 层数

[zhjs0505]

引用 1 楼 roy_88 的回复:

SELECT 编码 
,MAX(CASE WHEN Tab=1 THEN 编码 END) AS 编码1
,MAX(CASE WHEN Tab=1 THEN 价格 END) AS 价格1
,MAX(CASE WHEN Tab=2 THEN 编码 END) AS 编码2
,MAX(CASE WHEN Tab=2 THEN 价格 END) AS 价格2
,MAX(CASE WHEN Tab=3 THEN 编码 END) AS 编码3
,MAX(CASE WHEN Tab=3 THEN 价格 END) AS 价格3
FROM 
(SELECT  层数,编码=编码1,价格=价格1,*,Tab=1 FROM    t1
UNION ALL
SELECT  *,Tab=2 FROM    t2
UNION ALL
SELECT  *,Tab=3 FROM    t3
) AS a
GROUP BY 层数

会有错误：使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。 如果把表达式数目设置成一样，又会提示多次为 'a' 指定了列 '层数' 麻烦看一下怎么解决

[中国风]

SELECT 编码 
,MAX(CASE WHEN Tab=1 THEN 编码 END) AS 编码1
,MAX(CASE WHEN Tab=1 THEN 价格 END) AS 价格1
,MAX(CASE WHEN Tab=2 THEN 编码 END) AS 编码2
,MAX(CASE WHEN Tab=2 THEN 价格 END) AS 价格2
,MAX(CASE WHEN Tab=3 THEN 编码 END) AS 编码3
,MAX(CASE WHEN Tab=3 THEN 价格 END) AS 价格3
FROM 
(SELECT  层数,编码=编码1,价格=价格1,Tab=1 FROM    t1
UNION ALL
SELECT  *,Tab=2 FROM    t2
UNION ALL
SELECT  *,Tab=3 FROM    t3
) AS a
GROUP BY 层数

多敲了"*"去掉