日期:2014-05-18  浏览次数:20515 次

简单又复杂,大家帮我看看怎么回事啊?
create TABLE t(pid INT,type VARCHAR(1),jiage1 INT,jiage2 INT,jiage3 INT)
INSERT INTO T
SELECT 1,'a',200,300,400 UNION ALL
SELECT 1,'b',100,500,400 UNION ALL
SELECT 2,'b',100,500,400 UNION ALL
SELECT 2,'c',300,500,400

select * from 
(select a.pid,a.type,a.jiage1,a.jiage2,a.jiage3,
b.type,b.jiage1,b.jiage2,b.jiage3 
from t a full join t b on a.pid=b.pid and a.type<>b.type) as h


错误: 多次为 'h' 指定了列 'type'。 这个是怎么回事,大家帮我看看啊




------解决方案--------------------
SQL code

select * from  
(
    select 
    a.pid,a.type as atype,a.jiage1 as ajiage1,a.jiage2 as ajiage2,a.jiage3 as ajiage3,
    b.type as btype,b.jiage1 as bjiage1,b.jiage2 as bjiage2,b.jiage3 as bjiage3  
    from t a full join t b 
    on a.pid=b.pid and a.type<>b.type
) 
as h
/*
pid         atype ajiage1     ajiage2     ajiage3     btype bjiage1     bjiage2     bjiage3
----------- ----- ----------- ----------- ----------- ----- ----------- ----------- -----------
1           a     200         300         400         b     100         500         400
1           b     100         500         400         a     200         300         400
2           b     100         500         400         c     300         500         400
2           c     300         500         400         b     100         500         400
*/