求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
分享到:
------解决方案-------------------- select number,isnull((select col2 from temp_order where col1=number),'') as col2 from master..spt_values where type='p' and number between 1 and (select MAX(col1) from temp_order)
------解决方案-------------------- if object_id('temp_order','u') is not null
drop table temp_order
create table temp_order (col1 int,col2 varchar(10))
insert into temp_order values ('1','A'),('3','B'), ('4','C')
select * from temp_order
;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