http://topic.csdn.net/u/20090727/15/9ed67fff-9b1b-419e-a3c0-73daf0bff9b9.html?seed=471792301&r=58644993#r_58644993http://topic.csdn.net/u/20090727/15/9ed67fff-9b1b-419e-a3c0-73daf0bff9b9.html这个之前没有完全解决,另开贴。table1,结构如下: codeid int,FQ decimal(18,2), fdate datetime(销售时间) 这个表是记录每个销售员的销售记录,现在我需要按时间先后奖励前100名销售额首先达10000的销售员,CODEID每天都有销售记录,如下
insert into table1(codeid ,FQ ,fdate)
select 1,100,'2009-06-25 01:01:20'
union all
select 1,100,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
.......................................
假如codeid=1 从2009-06-25 到2009-07-01 20:06:20 销售额累计达到10000,输出CODEID=1,格式codeid,sum(fq),达到10000那时的时间(2009-07-01 20:06:20 ),三个字段
insert into table1(codeid ,FQ ,fdate)
select 1,100,'2009-06-25 01:01:20'
union all
select 1,100,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
.......................................
假如codeid=1 从2009-06-25 到2009-07-01 20:06:20 销售额累计达到10000,输出CODEID=1,格式codeid,sum(fq),达到10000那时的时间(2009-07-01 20:06:20 ),三个字段
SUM(FQ),(select max(fdate) from table1 where t.fdate=fdate) as fdate
from table1 t
group by codeid
having SUM(fq)>=10000
SUM(FQ),(select max(fdate) from table1 where t.codeid=codeid) as fdate
from table1 t
group by codeid
having SUM(fq)>=10000
select * from (
select codeid,Sum(fq) as fq ,Max(Fdate) as fdate,50 as '销售量' from a
where fdate>=@FromDate and fdate<@ToDate
group by codeid) b
where fq>=50
select * from (
select codeid,Sum(fq) as fq ,Max(Fdate) as fdate,50 as '销售量' from a
where fdate>=@FromDate and fdate<@ToDate
group by codeid) b
where fq>=销售量
SUM(FQ),
(select min(fdate) from table1
where t.codeid=codeid and group by codeid having SUM(fq)>=10000 )
from table1 t
group by codeid
having SUM(fq)>=10000
select top 100 codeid,
SUM(FQ),
(select fdate from table1
where t.codeid=codeid and (select SUM(fq) from table1 where fid<=tb.fid )=10000 )
from table1 t
group by codeid
having SUM(fq)>=10000
select identity(int,1,1) as fid,* into # from table1
select top 100 codeid,
SUM(FQ),
fdate=(select fdate from # tb
where t.codeid=#.codeid and (select SUM(fq) from # where fid<=tb.fid )=10000 )
from # t
group by codeid
having SUM(fq)>=10000
最后修改
————————————加order by Fdate,还有,你的表名是否有点混乱。
create table borwin(codeid int,FQ decimal(18,2), fdate datetime)
delete borwin
insert into borwin(codeid ,FQ ,fdate)
select 1,8000,'2009-06-25 01:01:20'
union all
select 1,3000,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
union all
select 2,3000,'2009-06-25 03:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
union all
select 3,20000,'2009-06-25 06:01:20'
select top 2 codeid,max(fdate),
(select sum(FQ) from borwin where codeid = A.codeid ) as 销售额
from borwin A
where (select sum(FQ) from borwin where codeid = A.codeid ) > 10000
group by codeid=========================
1 2009-06-25 06:01:20.000 11200.00
3 2009-06-25 06:01:20.000 20000.00
select identity(int,1,1) as fid,* into # from table1
select top 100 codeid,
SUM(FQ),
fdate=(select fdate from # tb
where t.codeid=#.codeid and (select SUM(fq) from # where fid<=tb.fid )=10000 )
from # t
group by codeid
having SUM(fq)>=10000
order by Fdate
可以执行的吧?
codeid
,fq=(select sum(FQ) from table1 where codeid=a.codeid)
,fdate
from table1 a
where (select sum(FQ) from table1 where codeid=a.codeid and fdate<=a.fdate)>=10000
and (select sum(FQ) from table1 where codeid=a.codeid and fdate<a.fdate)<10000
order by fdate
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE TABLE1
CREATE TABLE table1(codeid int,FQ decimal(18,2), fdate datetime)
insert into table1(codeid ,FQ ,fdate)
select 1,300,'2009-06-25 01:01:20'
union all
select 1,200,'2009-06-26 03:01:20'
union all
select 1,200,'2009-06-27 06:01:20'
union all
select 2,200,'2009-06-28 06:01:20'
union all
select 2,100,'2009-06-29 01:01:20'
union all
select 2,100,'2009-06-30 03:01:20'
union all
select 3,600,'2009-07-1 06:01:20'
union all
select 1,400,'2009-07-2 06:01:20'
union all
select 2,200,'2009-08-3 06:01:20'
union all
select 2,100,'2009-09-4 01:01:20'
union all
select 2,100,'2009-10-5 03:01:20'
union all
select 3,600,'2009-11-6 06:01:20'
union all
select 3,600,'2009-11-7 06:01:20'SELECT TOP 100 * FROM(
SELECT T1.CODEID,T1.FDATE,SUM(T2.FQ) 'FQ' FROM TABLE1 T1
INNER JOIN TABLE1 T2 ON T2.CODEID=T1.CODEID AND DATEDIFF(SS,T2.FDATE,T1.FDATE)>=0
GROUP BY T1.CODEID,T1.FQ ,T1.FDATE HAVING SUM(T2.FQ)>1000) T1
WHERE NOT EXISTS(SELECT DISTINCT 1 FROM (
SELECT T1.CODEID,T1.FDATE,SUM(T2.FQ) 'FQ' FROM TABLE1 T1
INNER JOIN TABLE1 T2 ON T2.CODEID=T1.CODEID AND DATEDIFF(SS,T2.FDATE,T1.FDATE)>=0
GROUP BY T1.CODEID,T1.FQ ,T1.FDATE HAVING SUM(T2.FQ)>1000
) T2 WHERE T2.CODEID=T1.CODEID AND DATEDIFF(SS,T2.FDATE,T1.FDATE)>0)--不会用WITH,多写了不少代码
/*
1 2009-07-02 06:01:20.000 1100.00
3 2009-11-06 06:01:20.000 1200.00
*/
create table borwin(codeid int,FQ decimal(18,2), fdate datetime)
delete borwin
insert into borwin(codeid ,FQ ,fdate)
select 1,8000,'2009-06-25 01:01:20'
union all
select 1,3000,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
union all
select 2,3000,'2009-06-25 03:01:20'
union all
select 2,10000,'2009-06-25 06:01:20'
union all
select 2,20000,'2009-06-25 06:21:20'
select top 2 codeid,max(fdate),
(select sum(FQ) from borwin where codeid = A.codeid ) as 销售额
from borwin A
where (select sum(FQ) from borwin where codeid = A.codeid ) > 10000
group by codeid
drop table borwin 时间那里不对,我只需要满足10000的那条,下面满足条件的,都不需要计算。feixianxxx:测试不通过
delete borwin
insert into borwin(codeid ,FQ ,fdate)
select 1,8000,'2009-06-25 01:01:20'
union all
select 1,3000,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
union all
select 2,3000,'2009-06-25 03:01:20'
union all
select 2,10000,'2009-06-25 06:01:20'
union all
select 3,20000,'2009-06-25 06:21:20' select identity(int,1,1) as fid,* into kpo3 from borwin order by codeid,Fdate,FQ
select codeid ,fqq=(select SUM(fq) from kpo3 where fID<=tb.fid and tb.codeid=codeid ),fdate
into T
from kpo3 tb
WHERE (select SUM(fq) from kpo3 where fID<=tb.fid and tb.codeid=codeid )>=10000
SELECT T.CODEID,FQQ,T.fdate
FROM t
where not exists(select * from t o where t.codeid=o.codeid and t.fqq <o.fqq)
/*
CODEID FQQ fdate
----------- --------------------------------------- -----------------------
1 11200.00 2009-06-25 06:01:20.000
2 13200.00 2009-06-25 06:01:20.000
3 20000.00 2009-06-25 06:21:20.000(3 行受影响)
*/
create table borwin(codeid int,FQ decimal(18,2), fdate datetime)
delete borwin
insert into borwin(codeid ,FQ ,fdate)
select 1,8000,'2009-06-25 01:01:20'
union all
select 1,3000,'2009-06-25 03:01:20'
union all
select 1,200,'2009-06-25 06:01:20'
union all
select 2,200,'2009-06-25 06:01:20'
union all
select 2,3000,'2009-06-25 03:01:20'
union all
select 2,10000,'2009-06-25 06:01:20'
union all
select 3,20000,'2009-06-25 06:21:20' select identity(int,1,1) as fid,* into kpo3 from borwin order by codeid,Fdate,FQ
select codeid ,fqq=(select SUM(fq) from kpo3 where fID<=tb.fid and tb.codeid=codeid ),fdate
into T
from kpo3 tb
WHERE (select SUM(fq) from kpo3 where fID<=tb.fid and tb.codeid=codeid )>=10000
SELECT T.CODEID,FQQ,T.fdate
FROM t
where not exists(select * from t o where t.codeid=o.codeid and t.fqq >o.fqq)
/*
CODEID FQQ fdate
----------- --------------------------------------- -----------------------
1 11000.00 2009-06-25 03:01:20.000
2 13200.00 2009-06-25 06:01:20.000
3 20000.00 2009-06-25 06:21:20.000*/