表t1 PDDay DHSUM XHSUM
11.1 10 5
11.7 15 8
11.15 20 12
11.21 21 16要得到一个这样的表
PDday DHINC XHINC
11.7 5 3
11.15 5 4
11.21 1 4
就是得到一个比上次增长的量的表。 SQL语句怎么写
11.1 10 5
11.7 15 8
11.15 20 12
11.21 21 16要得到一个这样的表
PDday DHINC XHINC
11.7 5 3
11.15 5 4
11.21 1 4
就是得到一个比上次增长的量的表。 SQL语句怎么写
select *,ROW_NUMBER()over(order by pdday) as rowid
from TB)select a.pdday,a.dhsum-b.dhsum as dhinc,a.xhsum-b.xhsum xhinc
from TT as a inner join TT as b on a.rowid=b.rowid+1
CREATE TABLE t1
(
PDDay FLOAT,
DHSUM INT,
XHSUM INT
)
INSERT INTO t1
SELECT 11.1,10,5 UNION ALL
SELECT 11.7,15,8 UNION ALL
SELECT 11.15,20,12 UNION ALL
SELECT 11.21,21,16
SELECT * FROM t1;WITH AAA AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CAST('2012.'+LTRIM(PDDay) AS DATETIME)) AS RowIndex,* FROM t1
)
SELECT A.PDDay,A.DHSUM-B.DHSUM AS DHINC,A.XHSUM-B.XHSUM AS XHINC
FROM AAA AS A INNER JOIN AAA AS B ON A.RowIndex=B.RowIndex+1-------------------
PDDay DHINC XHINC
11.7 5 3
11.15 5 4
11.21 1 4楼主你PDDay字段真蛋疼,存放的月.日格式还要转换,还不如直接存放一个完整的datetime类型,或者date类型也好。不然排序的时候还要转换成datetime类型,否则11.7比11.21,11.15都大了。