http://topic.csdn.net/u/20090727/15/9ed67fff-9b1b-419e-a3c0-73daf0bff9b9.html?seed=471792301&r=58644993#r_58644993 http://topic.csdn.net/u/20090727/15/9ed67fff-9b1b-419e-a3c0-73daf0bff9b9.html
http://topic.csdn.net/u/20090728/14/f073d7e1-5408-4055-9257-f45680f66c77.html?seed=416709413&r=58669821#r_58669821需要按时间先后奖励前100名销售额首先达10000的销售员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'
drop table borwin 要求输出:
1,11000,2009-06-25 03:01:20 --注意:1,200,'2009-06-25 06:01:20' 这行记录不计算了。
2,13200,2009-06-25 06:01:20
3,20000,2009-06-25 06:21:20
http://topic.csdn.net/u/20090728/14/f073d7e1-5408-4055-9257-f45680f66c77.html?seed=416709413&r=58669821#r_58669821需要按时间先后奖励前100名销售额首先达10000的销售员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'
drop table borwin 要求输出:
1,11000,2009-06-25 03:01:20 --注意:1,200,'2009-06-25 06:01:20' 这行记录不计算了。
2,13200,2009-06-25 06:01:20
3,20000,2009-06-25 06:21:20
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)
ORDER BY T1.FDATE ASC
--不会用WITH,多写了不少代码
/*
1 2009-07-02 06:01:20.000 1100.00
3 2009-11-06 06:01:20.000 1200.00
*/--为什么要发这么多贴
CREATE TABLE table1(codeid int,FQ decimal(18,2), fdate datetime)
insert into table1(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 TOP 100 * FROM(
SELECT DISTINCT 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)>10000) 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)>10000
) T2 WHERE T2.CODEID=T1.CODEID AND DATEDIFF(SS,T2.FDATE,T1.FDATE)>0)
ORDER BY T1.FDATE ASC
--不会用WITH,多写了不少代码
/*
1 2009-06-25 03:01:20.000 11000.00
2 2009-06-25 06:01:20.000 13200.00
3 2009-06-25 06:21:20.000 20000.00--终于有实际数据了
*/
建议 你试下: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>=销售量
select top 100 * 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>=销售量
select top 100 * 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>=销售量我的原理是这样,先将时间段内的数据全部按codeid统计起来,然后定义一个销售量。
如果 每个 codeid 的 fq 超过了你设定的 销售量 则显示出来。不对了么?
from borwin a
join
(
select top 100 codeid,fdate
from borwin t
where (select sum(FQ) from borwin where codeid=t.codeid and fdate<=t.fdate)>=10000
and (select isnull(sum(FQ),0) from borwin where codeid=t.codeid and fdate<t.fdate)<10000
group by codeid,fdate
order by fdate
) b
on a.codeid=b.codeid and a.fdate<=b.fdate
group by a.codeid
/*
codeid fq 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(3 行受影响)
*/
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 * from borwin order by fdate
;
with cte1 as
(
select codeid,min(fdate) fdate from
(
select codeid,fdate,dif=(select sum(fq) from borwin where codeid=t.codeid and fdate<=t.fdate)
from borwin t
) tmp
where dif>10000 group by codeid
)select codeid,fq=(select sum(fq) from borwin where codeid=t.codeid and fdate<=t.fdate),fdate from cte1 t
/*
codeid fq 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(3 行受影响)
*/
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 行受影响)
*/
楼上的大大们。。用CASE 语句可以吗。
select codeid,
sum(case when FQ=200 and codeid=1 and fdate='2009-06-25 06:01:20.000' then 0 else FQ end)FQ,
max(case when FQ=200 and codeid=1 and fdate='2009-06-25 06:01:20.000' then 0 else fdate end)FDATE
from borwin group by codeid---------------------------------------------------
codeid FQ 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 貌似超简单
修改下
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*/