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

pennymay 2009-04-02 08:21:13
表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

谢谢大家
...全文
989 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
htl258_Tony 2009-04-02
  • 打赏
  • 举报
回复
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
htl258_Tony 2009-04-02
  • 打赏
  • 举报
回复
[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
hndth 2009-04-02
  • 打赏
  • 举报
回复
友情up
竹叶青suhy 2009-04-02
  • 打赏
  • 举报
回复
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)
qizhengsheng 2009-04-02
  • 打赏
  • 举报
回复
可以jf不?
ws_hgo 2009-04-02
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 fan_xiaohu 的回复:]
打扰一下2楼,您怎么知道'1','3'在两个表里会存在呢~
[/Quote]
2楼的不好
用我的
应该是先链接然后在union
you_tube 2009-04-02
  • 打赏
  • 举报
回复
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 2009-04-02
  • 打赏
  • 举报
回复
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 行受影响)
small_well 2009-04-02
  • 打赏
  • 举报
回复

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
fan_xiaohu 2009-04-02
  • 打赏
  • 举报
回复
打扰一下2楼,您怎么知道'1','3'在两个表里会存在呢~
fan_xiaohu 2009-04-02
  • 打赏
  • 举报
回复

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)
dpzc_love 2009-04-02
  • 打赏
  • 举报
回复

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 )
claro 2009-04-02
  • 打赏
  • 举报
回复
/*
表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
playwarcraft 2009-04-02
  • 打赏
  • 举报
回复
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)

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧