if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID INT IDENTITY(1,1),fenshu1 int,fenshu2 int,name varchar(20))
insert into #A
select 12,11,'a'union all
select 3,12,'b'union all
select 34,2,'c'union all
select 4,13,'d'union all
select 3,12,'e'union all
select 12,11,'f'select * from #a
求下面结果
id name fenshu leixing
1 a 12 fenshu1
1 a 11 fenshu2
1 b 2 fenshu1
1 b 12 fenshu2
1 c 34 fenshu1
.....
create table #A (ID INT IDENTITY(1,1),fenshu1 int,fenshu2 int,name varchar(20))
insert into #A
select 12,11,'a'union all
select 3,12,'b'union all
select 34,2,'c'union all
select 4,13,'d'union all
select 3,12,'e'union all
select 12,11,'f'select * from #a
求下面结果
id name fenshu leixing
1 a 12 fenshu1
1 a 11 fenshu2
1 b 2 fenshu1
1 b 12 fenshu2
1 c 34 fenshu1
.....
select id,name,fenshu2 fenshu, 'fenshu2' leixing from a order by name,leixing
SELECT 1 AS id,t.name,t.fengshu,t.leixing
FROM
(
select name,fenshu1 AS fengshu,'fenshu1' AS leixing from #a
UNION ALL
select name,fenshu2,'fenshu2' from #a
) AS t
ORDER BY t.name
id name fengshu leixing
----------- -------------------- ----------- -------
1 a 12 fenshu1
1 a 11 fenshu2
1 b 12 fenshu2
1 b 3 fenshu1
1 c 34 fenshu1
1 c 2 fenshu2
1 d 13 fenshu2
1 d 4 fenshu1
1 e 3 fenshu1
1 e 12 fenshu2
1 f 11 fenshu2
1 f 12 fenshu1(12 行受影响)