select * from( select * from tab1 union all select id2,mc2 from tab2 )a order by id
--测试--测试数据 create table TAB1(ID varchar(10),MC varchar(10)) insert tab1 select '01','A' union all select '02','B' union all select '03','C'create table TAB2(ID_H varchar(10),ID2 varchar(10),MC2 varchar(10)) insert tab2 select '01','0101','a1' union all select '01','0102','a2' union all select '02','0201','b1' union all select '03','0301','c1' go--查询 select * from( select * from tab1 union all select id2,mc2 from tab2 )a order by id go--删除测试 drop table tab1,tab2/*--测试结果ID MC ---------- ---------- 01 A 0101 a1 0102 a2 02 B 0201 b1 03 C 0301 c1(所影响的行数为 7 行) --*/
select * from ( select * from tab1 union select id2,mc2 from tab2 ) a order by id
同意zjcxc(: 邹建 :) 并学习
create table tab1(id varchar(10),mc varchar(10))insert into tab1 select '01','A' union all select '02','B' union all select '03','C'create table tab2(id_h varchar(10),id2 varchar(10),mc2 varchar(10))insert into tab2 select '01','0101','a1' union all select '01','0102','a2' union all select '02','0201','b1' union all select '03','0301','c1' select a.id ID,a.mc MC from tab1 a union all select b.id2 ID,b.mc2 MC from tab2 b,tab1 a where b.id_h=a.id order by ID
SELECT ID,MC FROM TAB1 UNION SELECT ID2,MC2 FROM TAB2 ORDER BY ID
SELECT * FROM (SELECT ID,MC FROM TAB1 UNION SELECT ID2,MC2 FROM TAB2 ) ORDER BY ID
http://expert.csdn.net/Expert/topic/2862/2862783.xml?temp=.1901361
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.5049707
select * from tab1
union all
select id2,mc2 from tab2
)a order by id
create table TAB1(ID varchar(10),MC varchar(10))
insert tab1 select '01','A'
union all select '02','B'
union all select '03','C'create table TAB2(ID_H varchar(10),ID2 varchar(10),MC2 varchar(10))
insert tab2 select '01','0101','a1'
union all select '01','0102','a2'
union all select '02','0201','b1'
union all select '03','0301','c1'
go--查询
select * from(
select * from tab1
union all
select id2,mc2 from tab2
)a order by id
go--删除测试
drop table tab1,tab2/*--测试结果ID MC
---------- ----------
01 A
0101 a1
0102 a2
02 B
0201 b1
03 C
0301 c1(所影响的行数为 7 行)
--*/
(
select * from tab1
union
select id2,mc2 from tab2
) a
order by id
并学习
union all select '02','B'
union all select '03','C'create table tab2(id_h varchar(10),id2 varchar(10),mc2 varchar(10))insert into tab2 select '01','0101','a1'
union all select '01','0102','a2'
union all select '02','0201','b1'
union all select '03','0301','c1'
select a.id ID,a.mc MC from tab1 a
union all
select b.id2 ID,b.mc2 MC from tab2 b,tab1 a where b.id_h=a.id order by ID
UNION
SELECT ID2,MC2 FROM TAB2
ORDER BY ID
UNION
SELECT ID2,MC2 FROM TAB2
)
ORDER BY ID