一个SQL查询问题，急急急！！！！！
PROD PRODQTY OPR QTY DATE
A01 100 A 5 2012-01-01
A01 100 B 25 2012-01-02
A01 100 C 35 2012-01-03
A01 100 D 15 2012-01-04
A01 100 E 15 2012-01-05
A01 100 f 10 2012-01-06
A01 100 g 12 2012-01-07
A02 50 Aa 5 2012-02-01
A02 50 Ba 10 2012-02-02
A02 50 Ca 20 2012-02-03
A02 50 Da 16 2012-02-04
A02 50 Ea 15 2012-02-05
A02 50 fa 10 2012-02-06
A02 50 ga 12 2012-02-07
... ... ... ... ...
根据PROD,PRODQTY，汇总QTY，按DATE升序排列，当汇总数大于或等于PRODQTY的话
那么就取出来那一条记录（每个PRODI只取一条记录）
如：
PROD PRODQTY OPR QTY DATE
A01 100 E 10 2012-01-05
A02 50 Da 16 2012-02-04
... ... ... ... ...
[解决办法]
with TB(PROD,PRODQTY,OPR,QTY ,DATE)as
(
select 'A01',100,'A',5,'2012-01-01' union all
select 'A01',100,'B',25,'2012-01-02' union all
select 'A01',100,'C',35,'2012-01-03' union all
select 'A01',100,'D',15,'2012-01-04' union all
select 'A01',100,'E',15,'2012-01-05' union all
select 'A01',100,'f',10,'2012-01-06' union all
select 'A01',100,'g',12,'2012-01-07' union all
select 'A02',50,'Aa',5,'2012-02-01' union all
select 'A02',50,'Ba',10,'2012-02-02' union all
select 'A02',50,'Ca',20,'2012-02-03' union all
select 'A02',50,'Da',16,'2012-02-04' union all
select 'A02',50,'Ea',15,'2012-02-05' union all
select 'A02',50,'fa',10,'2012-02-06' union all
select 'A02',50,'ga',12,'2012-02-07'
)
select * from tb where prod+date in
(select prod+date from
(select row_number() over(partition by a.prod order by a.prod)number,a.PROD,a.PRODQTY,a.date,SUM(b.QTY)qty
from tb a, TB b where a.DATE>=b.DATE and a.PROD=b.PROD
group by a.PROD,a.PRODQTY,a.date having SUM(b.QTY)>a.PRODQTY)a where number=1)
SELECT TB.*
FROM TB
INNER JOIN ( SELECT TOP 1
A.date ,
A.prodqty ,
A.PROD ,
SUM(B.QTY) AS qty
FROM dbo.TB A
INNER JOIN TB B ON A.date >= B.date
GROUP BY A.date ,
A.prodqty ,
A.PROD
HAVING SUM(B.QTY) <= A.prodqty
ORDER BY a.date DESC
) T ON T.date = TB.date
AND T.prod = TB.prod
UNION ALL
SELECT TB.*
FROM TB
INNER JOIN ( SELECT TOP 1
A.date ,
A.prodqty ,
A.PROD ,
SUM(B.QTY) AS qty
FROM dbo.TB A
INNER JOIN TB B ON A.date <= B.date AND A.prod =B.prod
GROUP BY A.date ,
A.prodqty ,
A.PROD
HAVING SUM(B.QTY) > A.prodqty
ORDER BY a.date DESC
) T ON T.date = TB.date
AND T.prod = TB.prod
create table tb(PROD varchar(10),PRODQTY INT,OPR varchar(10),QTY int,DATE datetime)
insert into tb select '1',100,'A',5,'2012-01-01'
insert into tb select '1',100,'B',25,'2012-01-02'
insert into tb select '1',100,'C',35,'2012-01-03'
insert into tb select '1',100,'D',15,'2012-01-04'
insert into tb select '1',100,'E',15,'2012-01-05'
insert into tb select '1',100,'f',10,'2012-01-06'
insert into tb select '1',100,'g',12,'2012-01-07'
insert into tb select '2',50,'Aa',5,'2012-02-01'
insert into tb select '2',50,'Ba',10,'2012-02-02'
insert into tb select '2',50,'Ca',20,'2012-02-03'
insert into tb select '2',50,'Da',16,'2012-02-04'
insert into tb select '2',50,'Ea',15,'2012-02-05'
insert into tb select '2',50,'fa',10,'2012-02-06'
insert into tb select '2',50,'ga',12,'2012-02-07'
go
;with cte as(
select * from tb a where exists(select 1 from tb b where prod=a.prod and date<=a.date group by prod having sum(qty)>=a.prodqty)
)select * from cte a where not exists(select 1 from cte where prod=a.prod and date<a.date)
/*
PROD PRODQTY OPR QTY DATE
---------- ----------- ---------- ----------- -----------------------
1 100 f 10 2012-01-06 00:00:00.000
2 50 Da 16 2012-02-04 00:00:00.000
(2 行受影响)
*/
go
drop table tb
