34,838
社区成员




select a.* from a join b on a.id=b.id
union
select b.* from a join b on a.id=b.id
不能加那个all,Modifyselect a.* from a join b on a.id=b.id
union all
select b.* from a join b on a.id=b.id
select * from A where exists(select 1 from B where id=A.id)
union all
select * from B where exists(select 1 from A where id=B.id)
DECLARE @A table (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into @A
select 1,'shan','11-01-2009','A' union all
select 2,'shan','11-01-2009','B' union all
select 2,'shan','11-01-2009','D' union all
select 2,'shan','11-01-2009','E' union all
select 3,'shan','11-01-2009','D'
declare @B table (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into @B
select 1,'shan','12-01-2009','T' union all
select 3,'shan','12-01-2009','S' union all
select 3,'shan','12-01-2009','M' union all
select 4,'shan','12-01-2009','Z' union all
select 5,'shan','12-01-2009','S'
select * from @A A where exists(select 1 from @B where id=A.id)
union all
select * from @B B where exists(select 1 from @A where id=B.id)
/*
ID NAME TIME CODE
----------- -------------------- -------------------- --------
1 shan 11-01-2009 A
3 shan 11-01-2009 D
1 shan 12-01-2009 T
3 shan 12-01-2009 S
3 shan 12-01-2009 M*/
create table #A (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into #A
select 1,'shan','11-01-2009','A' union all
select 2,'shan','11-01-2009','B' union all
select 2,'shan','11-01-2009','D' union all
select 2,'shan','11-01-2009','E' union all
select 3,'shan','11-01-2009','D'
create table #B (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into #B
select 1,'shan','12-01-2009','T' union all
select 3,'shan','12-01-2009','S' union all
select 3,'shan','12-01-2009','M' union all
select 4,'shan','12-01-2009','Z' union all
select 5,'shan','12-01-2009','S'
select A.* from #A A join #B B on B.ID=A.ID
union
select B.* from #B B join #A A on B.ID=A.ID
ID NAME TIME CODE
----------- -------------------- -------------------- --------
1 shan 11-01-2009 A
1 shan 12-01-2009 T
3 shan 11-01-2009 D
3 shan 12-01-2009 M
3 shan 12-01-2009 S
(5 行受影响)
create table 表A(ID int,NAME varchar(10),TIME datetime,CODE char(2))
create table 表B(ID int,NAME varchar(10),TIME datetime,CODE char(2))
insert into 表A
select 1,'shan','11-01-2009','A' union all
select 2,'shan','11-01-2009','B' union all
select 2,'shan','11-01-2009','D' union all
select 2,'shan','11-01-2009','E' union all
select 3,'shan','11-01-2009','D'
insert into 表B
select 1,'shan','12-01-2009','T' union all
select 3,'shan','12-01-2009','S' union all
select 3,'shan','12-01-2009','M' union all
select 4,'shan','12-01-2009','Z' union all
select 5,'shan','12-01-2009','S'
select ID,NAME,TIME=convert(varchar(10),TIME,110),CODE from 表A where id in (select id from 表B)
union all
select ID,NAME,TIME=convert(varchar(10),TIME,110),CODE from 表B where id in (select id from 表A)
/*
结果
ID NAME TIME CODE
1 shan 11-01-2009 A
3 shan 11-01-2009 D
1 shan 12-01-2009 T
3 shan 12-01-2009 S
3 shan 12-01-2009 M
*/
drop table 表A
drop table 表B
select * from A WHERE A.ID IN (SELECT DISTINCT ID FROM B )
UNION ALL
select * from B WHERE B.ID IN (SELECT DISTINCT ID FROM A)
SELECT * FROM T1 A WHERE EXISTS(SELECT 1 FROM T2 B WHERE A.ID = B.ID )
UNION ALL
SELECT * FROM T2 A WHERE EXISTS(SELECT 1 FROM T1 B WHERE A.ID = B.ID )
/*
表A
ID NAME TIME CODE
1 shan 11-01-2009 A
2 shan 11-01-2009 B
2 shan 11-01-2009 D
2 shan 11-01-2009 E
3 shan 11-01-2009 D
表B
ID NAME TIME CODE
1 shan 12-01-2009 T
3 shan 12-01-2009 S
3 shan 12-01-2009 M
4 shan 12-01-2009 Z
5 shan 12-01-2009 S
表A Union 表B on 表A.ID = 表B.ID
结果
ID NAME TIME CODE
1 shan 11-01-2009 A
3 shan 11-01-2009 D
1 shan 12-01-2009 T
3 shan 12-01-2009 S
3 shan 12-01-2009 M
*/
create table #A (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into #A
select 1,'shan','11-01-2009','A' union all
select 2,'shan','11-01-2009','B' union all
select 2,'shan','11-01-2009','D' union all
select 2,'shan','11-01-2009','E' union all
select 3,'shan','11-01-2009','D'
create table #B (ID int ,[NAME] varchar(20),[TIME] varchar(20), CODE varchar(8))
insert into #B
select 1,'shan','12-01-2009','T' union all
select 3,'shan','12-01-2009','S' union all
select 3,'shan','12-01-2009','M' union all
select 4,'shan','12-01-2009','Z' union all
select 5,'shan','12-01-2009','S'
select * from #A where ID in ('1','3')
UNION
select * from #B where ID in ('1','3')
ID NAME TIME CODE
1 shan 11-01-2009 A
1 shan 12-01-2009 T
3 shan 11-01-2009 D
3 shan 12-01-2009 M
3 shan 12-01-2009 S