求select写法
有如下表：
create table temp_order (col1 int,col2 varchar(10))
insert into temp_order values ('1','A')
insert into temp_order values ('3','B')
insert into temp_order values ('4','C')
如何得到
1 A
2
3 B
4 C
;with T as
(select min(t.col1) col1_start,max(t.col1) col1_end from temp_order as t),
T1 as
(
select col1_start from T
union all
select t1.col1_start+1 from T1 as t1 inner join T as t on t1.col1_start<t.col1_end
)
select t.col1_start as col1,isnull(a.col2,'') from T1 as t
left join temp_order as a on t.col1_start = a.col1
[解决办法]
select b.number,isnull(a.col2,'')as col2
from
(
select * from master..spt_values where type='p' and number between 1 and (select MAX(col1) from temp_order)
)b left join
temp_order a on a.col1 = b.number
[解决办法]
select top 100 id=identity(int,1,1) into # from sysobjects
select t2.id col1,t1.col2 from # t2 left join temp_order t1
on t1.col1=t2.id
where id<=(select max(col1) from temp_order)
/*
col1 col2
----------- ----------
1 A
2 NULL
3 B
4 C
(4 row(s) affected)
*/
select t2.col1 col1,t1.col2 from temp_order t2 left join temp_order t1
on t1.col1=t2.col1
where id<=(select max(col1) from temp_order)
