---测试数据--- if object_id('[A]') is not null drop table [A] go create table [A]([num] int) insert [A] select 2 union all select 3 union all select 6 union all select 8 union all select 3 union all select 5
---查询--- ;with cte1 as (select rn=row_number() over(order by getdate()),* from a ) select isnull((select num from cte1 where rn=t.rn-1),0) as num from cte1 t ---结果--- num ----------- 0 2 3 6 8 3(6 行受影响)
if not object_id('ta') is null drop table ta Go Create table ta(ID int identity,[num] int) Insert ta select 2 union all select 3 union all select 6 union all select 8 union all select 3 union all select 5 Go declare @count int select @count=count(*) from ta select top (@count)* from( select 0 ID,0 [Num] union all select * from ta )t order by id /* ID Num ----------- ----------- 0 0 1 2 2 3 3 6 4 8 5 3(6 row(s) affected) */
我以前记录的是下面的,但是不明白那个select max(num)是什么意思,能讲解下么? if object_id('[A]') is not null drop table [A] go create table [A]([num] int) insert [A] select 2 union all select 3 union all select 6 union all select 8 union all select 3 union all select 5select coalesce((select max(num) from A as a where a.num<b.num),0) from A as b
直接union allselect 0 num union all select num from tb
if object_id('[A]') is not null drop table [A]
go
create table [A]([num] int)
insert [A]
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 3 union all
select 5
---查询---
;with cte1
as
(select rn=row_number() over(order by getdate()),* from a
)
select
isnull((select num from cte1 where rn=t.rn-1),0) as num
from
cte1 t
---结果---
num
-----------
0
2
3
6
8
3(6 行受影响)
drop table ta
Go
Create table ta(ID int identity,[num] int)
Insert ta
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 3 union all
select 5
Go
declare @count int
select @count=count(*) from ta
select top (@count)*
from(
select 0 ID,0 [Num]
union all
select * from ta
)t
order by id
/*
ID Num
----------- -----------
0 0
1 2
2 3
3 6
4 8
5 3(6 row(s) affected)
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([num] int)
insert [A]
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 3 union all
select 5select coalesce((select max(num) from A as a where a.num<b.num),0)
from A as b
union all
select num from tb
select 0
union all
select num from table
的话就多增加了行了