一个表查询数据计算

[2004v2004]

tabledy 表
有三个字段分别如下
rdate jq sl
2010-4-26 1号机 5657500
2010-4-27 1号机 5661330
2010-4-28 1号机 5661335
2010-4-29 1号机 5661339
2010-4-30 1号机 5661395
2010-5-4 1号机 5661401
2010-5-6 1号机 5662177

我想计算查询出成下面的
rdate jq sl 差值
2010-4-26 1号机 5657500 5657500
2010-4-27 1号机 5661330 5661335-5661330=3830
2010-4-28 1号机 5661335 5661335-5661330=5
2010-4-29 1号机 5661339 5661339-5661335
2010-4-30 1号机 5661395 5661395-5661339
2010-5-4 1号机 5661401 5661401-5661395
2010-5-6 1号机 5662177 5662177-5661401


得到一个这个样的结果


也可以
rdate jq 差值
2010-4-26 1号机 5657500
2010-4-27 1号机 5661335-5661330=3830
2010-4-28 1号机 5661335-5661330=5
2010-4-29 1号机 5661339-5661335
2010-4-30 1号机 5661395-5661339
2010-5-4 1号机 5661401-5661395
2010-5-6 1号机 5662177-5661401

这两个结果 均可以 该怎么写这个查询，谢谢了

[chuifengde]

SELECT *,cz=sl-isnull((SELECT TOP 1 sl FROM tabledy WHERE jq=a.jq AND rdate<a.rdate ORDER BY rdate desc),0)
FROM tabledy a

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-07 11:20:09
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([rdate] [datetime],[jq] [nvarchar](10),[sl] [int])
INSERT INTO [tb]
SELECT '2010-4-26','1号机','5657500' UNION ALL
SELECT '2010-4-27','1号机','5661330' UNION ALL
SELECT '2010-4-28','1号机','5661335' UNION ALL
SELECT '2010-4-29','1号机','5661339' UNION ALL
SELECT '2010-4-30','1号机','5661395' UNION ALL
SELECT '2010-5-4','1号机','5661401' UNION ALL
SELECT '2010-5-6','1号机','5662177'

SELECT * FROM [tb]

-->SQL查询如下:
;with t as
(
	select rn=row_number()over(partition by [jq] order by [rdate]),*
	from tb
)
select a.[rdate],a.[jq],a.sl,a.sl-isnull(b.sl,0) sldif
from t a
	left join t b
		on a.rn=b.rn+1
/*
rdate                   jq         sl          sldif
----------------------- ---------- ----------- -----------
2010-04-26 00:00:00.000 1号机        5657500     5657500
2010-04-27 00:00:00.000 1号机        5661330     3830
2010-04-28 00:00:00.000 1号机        5661335     5
2010-04-29 00:00:00.000 1号机        5661339     4
2010-04-30 00:00:00.000 1号机        5661395     56
2010-05-04 00:00:00.000 1号机        5661401     6
2010-05-06 00:00:00.000 1号机        5662177     776

(7 行受影响)
*/

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-05-07 11:20:09
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([rdate] [datetime],[jq] [nvarchar](10),[sl] [int])
INSERT INTO [tb]
SELECT '2010-4-26','1号机','5657500' UNION ALL
SELECT '2010-4-27','1号机','5661330' UNION ALL
SELECT '2010-4-28','1号机','5661335' UNION ALL
SELECT '2010-4-29','1号机','5661339' UNION ALL
SELECT '2010-4-30','1号机','5661395' UNION ALL
SELECT '2010-5-4','1号机','5661401' UNION ALL
SELECT '2010-5-6','1号机','5662177'

--SELECT * FROM [tb]

-->SQL查询如下:
;with t as
(
    select rn=row_number()over(partition by [jq] order by [rdate]),*
    from tb
)
select a.[rdate],a.[jq],a.sl,a.sl-isnull(b.sl,a.sl) sldif
from t a
    left join t b
        on a.rn=b.rn+1 and a.jq=b.jq

/*
rdate                   jq         sl          sldif
----------------------- ---------- ----------- -----------
2010-04-26 00:00:00.000 1号机        5657500     0
2010-04-27 00:00:00.000 1号机        5661330     3830
2010-04-28 00:00:00.000 1号机        5661335     5
2010-04-29 00:00:00.000 1号机        5661339     4
2010-04-30 00:00:00.000 1号机        5661395     56
2010-05-04 00:00:00.000 1号机        5661401     6
2010-05-06 00:00:00.000 1号机        5662177     776

(7 行受影响)
*/

modify

[zhengduan964532]

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([rdate] [datetime],[jq] [nvarchar](10),[sl] [int])
INSERT INTO [tb]
SELECT '2010-4-26','1号机','5657500' UNION ALL
SELECT '2010-4-27','1号机','5661330' UNION ALL
SELECT '2010-4-28','1号机','5661335' UNION ALL
SELECT '2010-4-29','1号机','5661339' UNION ALL
SELECT '2010-4-30','1号机','5661395' UNION ALL
SELECT '2010-5-4','1号机','5661401' UNION ALL
SELECT '2010-5-6','1号机','5662177'



SELECT * FROM tb 

ALTER TABLE tb ADD  id INT  IDENTITY(1,1)

SELECT rdate,jq,
sl-isnull((SELECT sl FROM tb WHERE jq=a.jq AND id=a.id-1 AND rdate <a.rdate),0)
FROM tb a