我要统计各个部门报价金额前三名的项目
项目管理表
TB_PROJECT_MANAGEMENT
(
PROJECTNUMBER,/项目编号/
DEPARTMENT,/部门/
Amount,/报价金额/
DDate，/项目签订时间/
MEMO/备注/
）
要列出详细信息
---------------------------------------------------------------
select * from TB_PROJECT_MANAGEMENT t0
where (select count(*)
from TB_PROJECT_MANAGEMENT t1
where t1.department=t0.department
and t1.amount>t0.amount
and t1.projectnumber<>t0.projectnumber)<3
没测试的。
---------------------------------------------------------------
select top 3 * from TB_PROJECT_MANAGEMENT where DEPARTMENT=部门 order by Amount desc
---------------------------------------------------------------
select max(PROJECTNUMBER),DEPARTMENT,Amount,max(DDate),max(MEMO)
from TB_PROJECT_MANAGEMENT
group by DEPARTMENT,Amount
order by DEPARTMENT ,Amount DESC
having count(DEPARTMENT)<=3
---------------------------------------------------------------
我来试试
SELECT A.PROJECTNUMBER,
A.DEPARTMENT,
A.Amount，
A.DDate,
A.MEMO
FROM TB_PROJECT_MANAGEMENT A
WHERE A.DEPARTMENT in (SELECT Top 3 DEPARTMENT FROM TB_PROJECT_MANAGEMENT GROUP BY DEPARTMENT)
ORDER BY (SELECT SUM(Amount) FROM TB_PROJECT_MANAGEMENT WHERE DEPARTMENT=A.DEPARTMENT) DESC
---------------------------------------------------------------
select * from TB_PROJECT_MANAGEMENT a
where a.projectnumber in
(select top 3 b.projectnumber from TB_PROJECT_MANAGEMENT b where b.department=a.department order by b.amount DESC)
order by department ASC
---------------------------------------------------------------
按钮的方法应该是对的，不过是不是有并列的情况？
select * from TB_PROJECT_MANAGEMENT t0
where isnull((select count(*)
from TB_PROJECT_MANAGEMENT t1
where t1.department=t0.department
and t1.amount>t0.amount
),0)<3
---------------------------------------------------------------
给一个交苯但可行的方法：
select a.* into #temp0 from leave_card a
select a.* into #temp1 from #temp0 a,
(select number = min(c.number) from #temp0 c,(select comp_code,amount = max(amount) from #temp0 group by comp_code) d
where c.comp_code = d.comp_code and c.amount = d.amount group by c.comp_code ) b
where a.number = b.number
delete #temp0 from #temp0 a,#temp1 b where a.number = b.number
insert into #temp1 select a.*from #temp0 a,
(select number = min(c.number) from #temp0 c,(select comp_code,amount = max(amount) from #temp0 group by comp_code) d
where c.comp_code = d.comp_code and c.amount = d.amount group by c.comp_code ) b
where a.number = b.number
delete #temp0 from #temp0 a,#temp1 b where a.number = b.number
insert into #temp1 select a.*from #temp0 a,
(select number = min(c.number) from #temp0 c,(select comp_code,amount = max(amount) from #temp0 group by comp_code) d
where c.comp_code = d.comp_code and c.amount = d.amount group by c.comp_code ) b
where a.number = b.number
delete #temp0 from #temp0 a,#temp1 b where a.number = b.number
select * from #temp1 order by comp_code
drop table #temp0 drop table #temp1
说明：我这里number为主键，comp_code相当于你的department
---------------------------------------------------------------
declare @tmpDp varchar(20)
create table #temp(PROJECTNUMBER varchar(10),
DEPARTMENT varchar(10),Amount money,DDate datetime)
declare cursorProcNames cursor for
select distinct @tmpDp = DEPARTMENT from TB_PROJECT_MANAGEMENT
FOR READ ONLY
open cursorProcNames
while 1 =1
begin
fetch next from cursorProcNames into @tmpDp
if @@fetch_status <> 0
break
insert into #temp
select top 3 PROJECTNUMBER,DEPARTMENT,Amount,DDate
from TB_PROJECT_MANAGEMENT where DEPARTMENT = @tmpDp order by Amount desc
end
close cursorProcNames
deallocate cursorProcNames
---------------------------------------------------------------
看来建索引挺重要的啊，向高手们学习！
---------------------------------------------------------------
不过有个问题：对AMOUNT相同的处理有问题：下面是我的测试
TO:icevi(按钮工厂)，
select PROJECTNUMBER,DEPARTMENT,Amount from TB_PROJECT_MANAGEMENT t0
where (select count(*)
from TB_PROJECT_MANAGEMENT t1
where t1.department=t0.department
and t1.amount>t0.amount
and t1.projectnumber<>t0.projectnumber)<3 ORDER BY DEPARTMENT ASC , AMOUNT DESC
结果：
PROJECTNUMBER DEPARTMENT Amount
------------- ----------- --------------------
9 1001 23423452
6 1001 766575
23 1001 645645
15 1002 4324535
22 1002 678768
7 1002 546457
24 1003 678979
16 1003 547657
13 1003 124324
18 1004 45745
21 1005 768678
25 1006 45645
26 1006 45645
27 1006 45645
28 1006 45645
19 1006 45645
请看DEPARTMENT1006的记录！
---------------------------------------------------------------
这就是各个部门报价金额前三名的项目的select结果
PROJECTNUMBER DEPARTMENT Amount
----------------------------------------
19 1006 45645
25 1006 45645
26 1006 45645
27 1006 45645
28 1006 45645
29 1006 23456
30 1006 12345
这就是正确的结果？人家外行都看出来了，有意思！
向Yang_(扬帆破浪) 、 OpenVMS(半知半解) 、wzsswz、 icevi(按钮工厂)、 zhangxdd(xudong) 等sql高手们学习，以后多多关照噢