---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-08-12 16:15:29 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(6),[wage] int,[wtime] datetime) insert [tb] select '吴泽',1000,'2014-07-12' union all select '吴泽',100,'2014-08-12' union all select '吴泽',150,'2014-09-12' union all select '王志军',2100,'2014-07-12' union all select '王志军',210,'2014-08-12' union all select '王志军',250,'2014-09-12' union all select '陈明哲',2300,'2014-07-12' union all select '陈明哲',320,'2014-08-12' union all select '丁丽萍',3500,'2014-07-12' union all select '丁丽萍',350,'2014-08-12' union all select '秦志敏',4500,'2014-07-12' union all select '秦志敏',540,'2014-08-12' --------------开始查询-------------------------- select *,isnull((select sum(wage) as wage from tb where name=a.name and wtime<a.wtime)-wage,wage) as wage1 from tb as a ----------------结果---------------------------- /* name wage wtime wage1 ------ ----------- ----------------------- ----------- 吴泽 1000 2014-07-12 00:00:00.000 1000 吴泽 100 2014-08-12 00:00:00.000 900 吴泽 150 2014-09-12 00:00:00.000 950 王志军 2100 2014-07-12 00:00:00.000 2100 王志军 210 2014-08-12 00:00:00.000 1890 王志军 250 2014-09-12 00:00:00.000 2060 陈明哲 2300 2014-07-12 00:00:00.000 2300 陈明哲 320 2014-08-12 00:00:00.000 1980 丁丽萍 3500 2014-07-12 00:00:00.000 3500 丁丽萍 350 2014-08-12 00:00:00.000 3150 秦志敏 4500 2014-07-12 00:00:00.000 4500 秦志敏 540 2014-08-12 00:00:00.000 3960 */
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([name] varchar(6),[wage] int,[wtime] datetime) insert [tb] select '吴泽',1000,'2014-07-12' union all select '吴泽',100,'2014-08-12' union all select '吴泽',150,'2014-09-12' union all select '王志军',2100,'2014-07-12' union all select '王志军',210,'2014-08-12' union all select '王志军',250,'2014-09-12' union all select '陈明哲',2300,'2014-07-12' union all select '陈明哲',320,'2014-08-12' union all select '丁丽萍',3500,'2014-07-12' union all select '丁丽萍',350,'2014-08-12' union all select '秦志敏',4500,'2014-07-12' union all select '秦志敏',540,'2014-08-12' --------------开始查询-------------------------- ; WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name] DESC,wtime) RN FROM [tb] ), cte1 AS ( SELECT a.rn,a.NAME,a.wtime ,(SELECT SUM(wage) FROM cte WHERE cte.NAME=a.NAME AND (cte.rn<=a.rn ))-b.wage wage FROM cte a INNER join (SELECT * FROM cte WHERE rn=1) b ON a.NAME=b.name ) SELECT t2.NAME,t1.wtime,t2.wage-t1.wage wage1 FROM cte1 t1 INNER JOIN cte t2 ON t1.NAME=t2.name WHERE t2.rn=1 ORDER BY t2.NAME DESC----------------结果---------------------------- /*NAME wtime wage1 吴泽 2014-07-12 00:00:00.000 1000 吴泽 2014-08-12 00:00:00.000 900 吴泽 2014-09-12 00:00:00.000 750 王志军 2014-07-12 00:00:00.000 2100 王志军 2014-08-12 00:00:00.000 1890 王志军 2014-09-12 00:00:00.000 1640 秦志敏 2014-07-12 00:00:00.000 4500 秦志敏 2014-08-12 00:00:00.000 3960 丁丽萍 2014-07-12 00:00:00.000 3500 丁丽萍 2014-08-12 00:00:00.000 3150 陈明哲 2014-07-12 00:00:00.000 2300 陈明哲 2014-08-12 00:00:00.000 1980 */
sorry 打错了 是name字段
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-08-12 16:15:29
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(6),[wage] int,[wtime] datetime)
insert [tb]
select '吴泽',1000,'2014-07-12' union all
select '吴泽',100,'2014-08-12' union all
select '吴泽',150,'2014-09-12' union all
select '王志军',2100,'2014-07-12' union all
select '王志军',210,'2014-08-12' union all
select '王志军',250,'2014-09-12' union all
select '陈明哲',2300,'2014-07-12' union all
select '陈明哲',320,'2014-08-12' union all
select '丁丽萍',3500,'2014-07-12' union all
select '丁丽萍',350,'2014-08-12' union all
select '秦志敏',4500,'2014-07-12' union all
select '秦志敏',540,'2014-08-12'
--------------开始查询--------------------------
select *,isnull((select sum(wage) as wage from tb where name=a.name and wtime<a.wtime)-wage,wage) as wage1 from tb as a
----------------结果----------------------------
/* name wage wtime wage1
------ ----------- ----------------------- -----------
吴泽 1000 2014-07-12 00:00:00.000 1000
吴泽 100 2014-08-12 00:00:00.000 900
吴泽 150 2014-09-12 00:00:00.000 950
王志军 2100 2014-07-12 00:00:00.000 2100
王志军 210 2014-08-12 00:00:00.000 1890
王志军 250 2014-09-12 00:00:00.000 2060
陈明哲 2300 2014-07-12 00:00:00.000 2300
陈明哲 320 2014-08-12 00:00:00.000 1980
丁丽萍 3500 2014-07-12 00:00:00.000 3500
丁丽萍 350 2014-08-12 00:00:00.000 3150
秦志敏 4500 2014-07-12 00:00:00.000 4500
秦志敏 540 2014-08-12 00:00:00.000 3960
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(6),[wage] int,[wtime] datetime)
insert [tb]
select '吴泽',1000,'2014-07-12' union all
select '吴泽',100,'2014-08-12' union all
select '吴泽',150,'2014-09-12' union all
select '王志军',2100,'2014-07-12' union all
select '王志军',210,'2014-08-12' union all
select '王志军',250,'2014-09-12' union all
select '陈明哲',2300,'2014-07-12' union all
select '陈明哲',320,'2014-08-12' union all
select '丁丽萍',3500,'2014-07-12' union all
select '丁丽萍',350,'2014-08-12' union all
select '秦志敏',4500,'2014-07-12' union all
select '秦志敏',540,'2014-08-12'
--------------开始查询--------------------------
; WITH cte AS
(SELECT *,ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name] DESC,wtime) RN FROM [tb]
),
cte1 AS
(
SELECT a.rn,a.NAME,a.wtime
,(SELECT SUM(wage) FROM cte WHERE cte.NAME=a.NAME AND (cte.rn<=a.rn ))-b.wage wage
FROM cte a INNER join (SELECT * FROM cte WHERE rn=1) b ON a.NAME=b.name
)
SELECT t2.NAME,t1.wtime,t2.wage-t1.wage wage1 FROM cte1 t1 INNER JOIN cte t2 ON t1.NAME=t2.name
WHERE t2.rn=1
ORDER BY t2.NAME DESC----------------结果----------------------------
/*NAME wtime wage1
吴泽 2014-07-12 00:00:00.000 1000
吴泽 2014-08-12 00:00:00.000 900
吴泽 2014-09-12 00:00:00.000 750
王志军 2014-07-12 00:00:00.000 2100
王志军 2014-08-12 00:00:00.000 1890
王志军 2014-09-12 00:00:00.000 1640
秦志敏 2014-07-12 00:00:00.000 4500
秦志敏 2014-08-12 00:00:00.000 3960
丁丽萍 2014-07-12 00:00:00.000 3500
丁丽萍 2014-08-12 00:00:00.000 3150
陈明哲 2014-07-12 00:00:00.000 2300
陈明哲 2014-08-12 00:00:00.000 1980
*/