http://topic.csdn.net/u/20090727/15/9ed67fff-9b1b-419e-a3c0-73daf0bff9b9.html?seed=471792301&r=58644993#r_58644993有一表t1(codeid int,FQ decimal(18,2), fid int identity(1,1)
insert into t1(codeid,fq)
select 1,200
union all
select 2,300
union all
select 1,400
union all
select 2,100
................ 现在按如下要求:注意 codeid 有重复值
按照FID从小到大(升序)sum(fq),将符合sum(fq)>=2000的前100个codeid
按codeid ,sum(fq)输出. 也就是说,将首先满2000的前100个CODEID查询出来。
insert into t1(codeid,fq)
select 1,200
union all
select 2,300
union all
select 1,400
union all
select 2,100
................ 现在按如下要求:注意 codeid 有重复值
按照FID从小到大(升序)sum(fq),将符合sum(fq)>=2000的前100个codeid
按codeid ,sum(fq)输出. 也就是说,将首先满2000的前100个CODEID查询出来。
top 100 fid,sum(fq)
from
t1
group by
fid
having
sum(fq)>=2000
order by
fid,sum(fq)
SELECT TOP 100
*
FROM(
SELECT
*,
sum_fq = (SELECT SUM(fq) FROM t1 B WHERE A.fid >= B.fid)
FROM t1 A
)AA
WHERE sum_fq <= 2000
ORDER BY fid
select
top 100 distinct(fid)
from
t1
group by
fid
having
sum(fq)>=2000
order by
fid,sum(fq)
table1,结构如下:codeid int,FQ decimal(18,2), fdate datetime(销售时间)这个表是记录每个销售员的销售记录,现在我需要奖励前100名首先销售额达10000的销售员,如果不够100,就将满足条件的那些查询出来。
group by codeid
having sum(fq)>=2
select
top 100 fid,codeid,sum(fq)
from
t1
group by
fid ,codeid
having
sum(fq)>=2000
order by
fid,sum(fq)
select
top 100 distinct(fid)
from
t1
group by
fid
having
sum(fq)>=2000
order by
fid,sum(fq)
from t1
where codeid in (
select top 100 codeid
from t1 a
where (select sum(FQ) from t1 where codeid=a.codeid and fid<=a.fid)>=2000
and (select sum(FQ) from t1 where codeid=a.codeid and fid<a.fid)<2000
order by fid)
group by codeid