DECLARE @TB TABLE(COL SMALLDATETIME) INSERT @TB SELECT '2009-12-13' UNION ALL SELECT '2009-12-15' UNION ALL SELECT '2009-12-24'SELECT COL,ISNULL(DATEDIFF(DAY,COL2,COL),0) AS DIF FROM ( SELECT COL,(SELECT TOP 1 COL FROM @TB WHERE COL<T.COL ORDER BY COL DESC) AS COL2 FROM @TB T ) T /* COL DIF ----------------------- ----------- 2009-12-13 00:00:00 0 2009-12-15 00:00:00 2 2009-12-24 00:00:00 9 */
try: select a.日期字段1,isnull(datediff(dd,max(b.日期字段1),a.日期字段1),0) from 表 a left join 表 b on a.日期字段1>b.日期字段1 group by a.日期字段1
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-20 16:08:19 IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb Go CREATE TABLE tb(col SMALLDATETIME) Go INSERT INTO tb SELECT '2009-12-13' UNION ALL SELECT '2009-12-15' UNION ALL SELECT '2009-12-24' GOSELECT * FROM TB with wang as(select row=row_number() over (order by col),col from tb) select s.col,cha=isnull(datediff(dd,t.col,s.col),0) from wang s left join wang t on s.row=t.row +12009-12-13 00:00:00 0 2009-12-15 00:00:00 2 2009-12-24 00:00:00 9
if object_id('tb') is not null drop table tb go create table tb([日期字段] datetime) insert tb select '2009-12-13' union all select '2009-12-15' union all select '2009-12-24' goselect a.[日期字段],datediff(dd,isnull(b.日期字段,a.日期字段),a.日期字段) as 运算得到字段 from ( select px=(select count(1) from tb where 日期字段<=t.日期字段),* from tb t ) a left join ( select px=(select count(1) from tb where 日期字段<=t.日期字段),* from tb t ) b on a.px=b.px+1 /* 日期字段 运算得到字段 ----------------------- ----------- 2009-12-13 00:00:00.000 0 2009-12-15 00:00:00.000 2 2009-12-24 00:00:00.000 9(3 行受影响)*/
create table tb(date datetime)insert tb values('2009-12-13') insert tb values('2009-12-15') insert tb values('2009-12-24')with a as (select date,row_number() over(order by date) row from tb) select a.date date,isnull(Datediff(dd,b.date,isnull(a.date,b.date)),0) 运算得到字段 from a left join a b on a.row-1=b.row /* date 运算得到字段 ----------------------- ----------- 2009-12-13 00:00:00.000 0 2009-12-15 00:00:00.000 2 2009-12-24 00:00:00.000 9 */
DECLARE @TB TABLE(日期字段1 SMALLDATETIME) INSERT @TB SELECT '2009-12-13' UNION ALL SELECT '2009-12-15' UNION ALL SELECT '2009-12-24' select a.日期字段1, isnull(datediff(day,(select top 1 日期字段1 from @TB b where b.日期字段1 < a.日期字段1 order by b.日期字段1 desc),a.日期字段1),0) from @TB a
INSERT @TB
SELECT '2009-12-13' UNION ALL
SELECT '2009-12-15' UNION ALL
SELECT '2009-12-24'SELECT COL,ISNULL(DATEDIFF(DAY,COL2,COL),0) AS DIF
FROM (
SELECT COL,(SELECT TOP 1 COL FROM @TB WHERE COL<T.COL ORDER BY COL DESC) AS COL2
FROM @TB T
) T
/*
COL DIF
----------------------- -----------
2009-12-13 00:00:00 0
2009-12-15 00:00:00 2
2009-12-24 00:00:00 9
*/
select a.日期字段1,isnull(datediff(dd,max(b.日期字段1),a.日期字段1),0) from 表 a left join 表 b on a.日期字段1>b.日期字段1 group by a.日期字段1
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-20 16:08:19
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(col SMALLDATETIME)
Go
INSERT INTO tb
SELECT '2009-12-13' UNION ALL
SELECT '2009-12-15' UNION ALL
SELECT '2009-12-24'
GOSELECT * FROM TB
with
wang as(select row=row_number() over (order by col),col from tb)
select s.col,cha=isnull(datediff(dd,t.col,s.col),0) from wang s left join wang t on s.row=t.row +12009-12-13 00:00:00 0
2009-12-15 00:00:00 2
2009-12-24 00:00:00 9
go
create table tb([日期字段] datetime)
insert tb select '2009-12-13'
union all select '2009-12-15'
union all select '2009-12-24'
goselect a.[日期字段],datediff(dd,isnull(b.日期字段,a.日期字段),a.日期字段) as 运算得到字段
from
(
select px=(select count(1) from tb where 日期字段<=t.日期字段),* from tb t
) a
left join
(
select px=(select count(1) from tb where 日期字段<=t.日期字段),* from tb t
) b
on a.px=b.px+1
/*
日期字段 运算得到字段
----------------------- -----------
2009-12-13 00:00:00.000 0
2009-12-15 00:00:00.000 2
2009-12-24 00:00:00.000 9(3 行受影响)*/
insert tb values('2009-12-15')
insert tb values('2009-12-24')with a as
(select date,row_number() over(order by date) row from tb)
select a.date date,isnull(Datediff(dd,b.date,isnull(a.date,b.date)),0) 运算得到字段
from a left join a b on a.row-1=b.row
/*
date 运算得到字段
----------------------- -----------
2009-12-13 00:00:00.000 0
2009-12-15 00:00:00.000 2
2009-12-24 00:00:00.000 9
*/
DECLARE @TB TABLE(日期字段1 SMALLDATETIME)
INSERT @TB
SELECT '2009-12-13' UNION ALL
SELECT '2009-12-15' UNION ALL
SELECT '2009-12-24'
select a.日期字段1,
isnull(datediff(day,(select top 1 日期字段1 from @TB b where b.日期字段1 < a.日期字段1 order by b.日期字段1 desc),a.日期字段1),0)
from @TB a