select id , name = name1 from tb union all select id , name = name2 from tb order by id , name
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([id] varchar(1),[name1] varchar(4),[name2] varchar(5)) insert [tb] select 'a','john','jack' union all select 'g','lulu','smith'select [id],[name1] as name from [tb] union select [id],[name2] as name from [tb]----------------- a jack a john g lulu g smith
create table tb(id varchar(10), name1 varchar(10), name2 varchar(10)) insert into tb values('a' , 'john' , 'jack') insert into tb values('g' , 'lulu' , 'smith') GOselect id , name = name1 from tb union all select id , name = name2 from tb order by id , name drop table tb/* id name ---------- ---------- a jack a john g lulu g smith(所影响的行数为 4 行) */
select [id],[name1] as name from [tb] union select [id],[name2] as name from [tb]
select id , name = name1 from tb union all select id , name = name2 from tb order by id
union all
select id , name = name2 from tb
order by id , name
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] varchar(1),[name1] varchar(4),[name2] varchar(5))
insert [tb]
select 'a','john','jack' union all
select 'g','lulu','smith'select [id],[name1] as name from [tb]
union
select [id],[name2] as name from [tb]-----------------
a jack
a john
g lulu
g smith
insert into tb values('a' , 'john' , 'jack')
insert into tb values('g' , 'lulu' , 'smith')
GOselect id , name = name1 from tb
union all
select id , name = name2 from tb
order by id , name
drop table tb/*
id name
---------- ----------
a jack
a john
g lulu
g smith(所影响的行数为 4 行)
*/
union
select [id],[name2] as name from [tb]
union all
select id , name = name2 from tb
order by id