表如下:
id empid pid amount date
2 3 3 24000.00 20121031
3 4 3 24000.00 20121031
4 5 4 24000.00 20121031
5 3 3 240000.00 20121030规则就是按照empid存在pid中的行把amount累加,加入条件date判断
如果条件为date<=20121031得出结果如下
id empid pid amount
2 3 3 244800.00
3 4 3 48000.00
4 5 4 24000.00
如果条件为date<=20121030得出结果如下
id empid pid amount
5 3 3 240000.00
id empid pid amount date
2 3 3 24000.00 20121031
3 4 3 24000.00 20121031
4 5 4 24000.00 20121031
5 3 3 240000.00 20121030规则就是按照empid存在pid中的行把amount累加,加入条件date判断
如果条件为date<=20121031得出结果如下
id empid pid amount
2 3 3 244800.00
3 4 3 48000.00
4 5 4 24000.00
如果条件为date<=20121030得出结果如下
id empid pid amount
5 3 3 240000.00
from tb where date<=... group by empid,pid
from tb ,(
select empid,pid,sum(amount) amount ,date
from tb where date<=@date
group by empid,pid,date
)a
where tb.empid=a.empid and tb.pid=a.pid and tb.date=a.date
表如下:
id empid pid amount date
2 3 3 24000.00 20121031
3 4 3 24000.00 20121031
4 5 4 24000.00 20121031
5 3 3 240000.00 20121030规则就是按照empid存在pid中的行把amount累加,加入条件date判断
如果条件为date<=20121031得出结果如下
id empid pid amount
2 3 3 288000.00
3 4 3 48000.00
4 5 4 24000.00
如果条件为date<=20121030得出结果如下
id empid pid amount
5 3 3 240000.00
FROM tb
WHERE date <= '20121031'
GROUP BY empid , pid
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] INT,[empid] INT,[pid] INT,[amount] NUMERIC(8,2),[date] DATETIME)
INSERT #tb
SELECT 2,3,3,24000.00,'20121031' UNION ALL
SELECT 3,4,3,24000.00,'20121031' UNION ALL
SELECT 4,5,4,24000.00,'20121031' UNION ALL
SELECT 5,3,3,240000.00,'20121030'
--------------开始查询--------------------------
SELECT [id] = MIN(id) , [empid] , [pid] , SUM([amount])
FROM #tb
WHERE [date] <= '20121031'
GROUP BY [empid] , [pid]
----------------结果----------------------------
/*
id empid pid (无列名)
2 3 3 264000.00
3 4 3 24000.00
4 5 4 24000.00
*/
--------------开始查询--------------------------
SELECT [id] = MIN(id) , [empid] , [pid] , SUM([amount])
FROM #tb
WHERE [date] <= '20121030'
GROUP BY [empid] , [pid]
----------------结果----------------------------
/*
id empid pid (无列名)
5 3 3 240000.00*/
阿汤哥你这个 [date] <= '20121031'
结果应该是
id empid pid amount
2 3 3 288000.00
3 4 3 48000.00
4 5 4 24000.00
你的第一行的结果错了,要把pid为3的全累加
INSERT #tb
SELECT 2,3,3,24000.00,'20121031' UNION ALL
SELECT 3,4,3,24000.00,'20121031' UNION ALL
SELECT 4,5,4,24000.00,'20121031' UNION ALL
SELECT 5,3,3,240000.00,'20121030'
--------------开始查询--------------------------select *,
(select SUM(amount) from #tb b
where (a.empid=b.pid and b.date<=a.date) or a.id=b.id)
from #tb a
if(object_id('a')is not null) drop table a
go
create table a
(
id int,
empid int,
pid int,
amount decimal(18,2),
date datetime
)
go
insert into a
select 2,3,3,24000.00,'20121031' union all
select 3,4,3,24000.00,'20121031' union all
select 4,5,4,24000.00,'20121031' union all
select 5,3,3,240000.00,'20121030'
select * from a
select id,empid,pid,amount= (select isnull(sum(amount),0) from a where b.empid = a.pid) from a as b
where date <= '2012-10-31 00:00:00.000'/*
id empid pid amount
----------- ----------- ----------- ---------------------------------------
2 3 3 288000.00
3 4 3 24000.00
4 5 4 0.00
5 3 3 288000.00(4 行受影响)
*/
select id,empid,pid,amount=(select isnull(sum(amount),0) from a where b.empid = a.pid) from a as b
where date <= '2012-10-30 00:00:00.000'
/*
id empid pid amount
----------- ----------- ----------- ---------------------------------------
5 3 3 288000.00(1 行受影响)*/