sql问题~~`
CREATE TABLE [A] (
[id] [bigint] NOT NULL ,
[id1] [int] NULL ,
[id2] [int] NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [B] (
[id] [bigint] NOT NULL ,
[id1] [int] NULL ,
[id2] [int] NULL ,
) ON [PRIMARY]
GO
insert into A values( '1 ', '1 ', '0 ' )
insert into A values( '2 ', '2 ', '0 ' )
insert into A values( '3 ', '3 ', '0 ' )
insert into B values( '1 ', '1 ', '3 ' )
insert into B values( '2 ', '2 ', '2 ' )
insert into B values( '3 ', '3 ', '1 ' )
两表的关系是 A.id=B.id2
select * from A left join B on A.id=B.id2
能得到这个:
id id1 id2 id id1 id2
-------------------------------------------------
1 1 0 3 3 1
2 2 0 2 2 2
3 3 0 1 1 3
我却想要这样的:
id id1 id2
----------------------
1 1 0 (A表)
3 3 1 (B表)
2 2 0 (A表)
2 2 2 (B表)
3 3 0 (A表)
1 1 3 (B表)
上周没有解决 现在在线求教~ 没有分了 实在对不起~
drop table A
drop table B
------解决方案-------------------- select id,id1,id2 from (
select id as f,* ,1 as c from A
union
select id2 as f,* ,2 as c from B) a
order by a.f,c