带条件的UNION, 或者看看如何写SQL

[pennymay]

表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

谢谢大家

[playwarcraft]

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)

[claro]

/*
表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

[dpzc_love]

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 )

[fan_xiaohu]

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)

[fan_xiaohu]

打扰一下2楼，您怎么知道'1','3'在两个表里会存在呢~

[small_well]

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

[ws_hgo]

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 行受影响)

[you_tube]

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*/

[ws_hgo]

[Quote=引用 5 楼 fan_xiaohu 的回复:]
打扰一下2楼，您怎么知道'1','3'在两个表里会存在呢~
[/Quote]
2楼的不好
用我的
应该是先链接然后在union

[qizhengsheng]

可以jf不？

[竹叶青suhy]

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)

[hndth]

友情up

[htl258_Tony]

[Quote=引用楼主 pennymay 的帖子:]
表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 …
[/Quote]

select a.* from a join b on a.id=b.id
union all 
select b.* from a join b on a.id=b.id

[htl258_Tony]

select a.* from a join b on a.id=b.id
union
select b.* from a join b on a.id=b.id

不能加那个all,Modify