请大家帮忙看一下这个sql怎么写（特别是高手，解决即给分）？

[qinhl99]

有一个表，里面有三个字段
projectId varchar(50),--项目编号
userName varchar(50),--用户
allload int //工作量
现在要统计每个用户工作量最大的前三个项目，请问怎么下，突然没有思路了

[qinhl99]

qq:258369002抱歉，我在公司不能上QQ，但你可以发邮件给我[e-mail]
谢谢这位老兄！

[terry_12]

SELECT b.*,
(SELECT COUNT(1)
FROM NewTable
WHERE userName = b.userName AND allload >= b.allload) AS NUM
FROM NewTable b
WHERE ((SELECT COUNT(1)
FROM NewTable
WHERE userName = b.userName AND allload >= b.allload) <= 3)
这样试下,别名不能这样用

[竹林听雨2005]

呵呵，好的做法，正确

[terry_12]

或者这样
SELECT projectId, userName, allload
FROM (SELECT b.*,
(SELECT COUNT(1)
FROM NewTable
WHERE userName = b.userName AND allload >= b.allload) AS NUM
FROM NewTable b) DERIVEDTBL
WHERE (NUM <= 3)

[qinhl99]

看来是对了，哈哈，抱歉，分有点少了，怎么办呢？

[terry_12]

先别给分,万一有并列的话,可能就有问题了.

[terry_12]

例如第2名并列2个以上,可能就只能查出第一名了..

[qinhl99]

什么意思？为什么会有重复的呢？
对了，我自己不能结贴啊，说什么回帖人大于给定分数

[terry_12]

比如排第一的只有一个那么NUM为1,排第2的有3个,那么NUM为4,所以只能取到第一个记录.
我没发过提问帖,所以也不知道为什么不能结

[flyzq]

create table boyd
(
projectId varchar(50),
userName varchar(50),
alload int
)

insert into boyd values ('项目一','boyd',50)
insert into boyd values ('项目二','boyd',10)
insert into boyd values ('项目三','boyd',30)
insert into boyd values ('项目四','boyd',80)
insert into boyd values ('项目五','boyd',120)
insert into boyd values ('项目六','boyd',1)

insert into boyd values ('项目一','you',150)
insert into boyd values ('项目二','you',70)
insert into boyd values ('项目三','you',500)
insert into boyd values ('项目四','you',60)
insert into boyd values ('项目五','you',120)
insert into boyd values ('项目六','you',112)

select * from boyd

declare @sql nvarchar(4000)
declare @con nvarchar(4000)


select @con='',@sql=''

select @con=@con+' union select * from ( select top 3 * from boyd where userName='''+convert(nvarchar(50),userName)+'''order by alload desc) a'
from boyd group by userName

select @sql=substring(@con,7,len(@con)-6)

select @sql=@sql+' order by username,alload desc'

exec(@sql)

[qinhl99]

terry_12(大撒发射点) 果然是牛人，一般不问别人问题，哈哈！
不会出现你所说的情况，因为每一人对于特定的项目只可能有一个总工作量（allLoad,我忘记告诉业务规则了，抱歉)

[竹林听雨2005]

create table boyd (
projectId varchar(50),
userName varchar(50),
alload int)

insert into boyd values ('项目一','boyd',50)
insert into boyd values ('项目二','boyd',10)
insert into boyd values ('项目三','boyd',30)
insert into boyd values ('项目四','boyd',80)
insert into boyd values ('项目五','boyd',120)
insert into boyd values ('项目六','boyd',1)
insert into boyd values ('项目一','you',150)
insert into boyd values ('项目二','you',70)
insert into boyd values ('项目三','you',500)
insert into boyd values ('项目四','you',500)
insert into boyd values ('项目五','you',500)
insert into boyd values ('项目六','you',500)
--方案一
SELECT b.*,
(SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) AS NUM
FROM boyd b
WHERE ((SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) <= 3)
--方案二
SELECT projectId, userName, alload
FROM (SELECT b.*,
(SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) AS NUM
FROM boyd b) DERIVEDTBL
WHERE (NUM <= 3)


这样则不能正确取到数据

[zhangxiaopin]

select top 3 * ,(select count(*)+1 from TB_Chengji b where b.chines>t.chines) as HeightChines
from TB_Chengji as t where name='zxp' order by HeightChines

[terry_12]

晕,不是这个意思.
我刚最近才上这里.
以前的问题都问同事解决的.

[qinhl99]

哈哈，怎么着也四个星了啊

[竹林听雨2005]

哈哈，我差16分升二星，

[andylauhai]

顶

[xbcin]

楼主不厚道!别人都给了你正确的答案了,你却不给别人分!你试试




create table boyd (
projectId varchar(50),
userName varchar(50),
alload int)

insert into boyd values ('项目一','boyd',50)
insert into boyd values ('项目二','boyd',10)
insert into boyd values ('项目三','boyd',30)
insert into boyd values ('项目四','boyd',80)
insert into boyd values ('项目五','boyd',120)
insert into boyd values ('项目六','boyd',1)
insert into boyd values ('项目一','you',150)
insert into boyd values ('项目二','you',70)
insert into boyd values ('项目三','you',500)
insert into boyd values ('项目四','you',500)
insert into boyd values ('项目五','you',500)
insert into boyd values ('项目六','you',500)
--方案一
SELECT b.*,
(SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) AS NUM
FROM boyd b
WHERE ((SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) <= 3)
--方案二
SELECT projectId, userName, alload
FROM (SELECT b.*,
(SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) AS NUM
FROM boyd b) DERIVEDTBL
WHERE (NUM <= 3)


这样则不能正确取到数据

[xbcin]

对了!顺便给楼主说一下,将刚才贴出的那段SQL修改一下就可以完美了


SELECT b.*,
(SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) AS NUM
FROM boyd b
WHERE ((SELECT COUNT(1)
FROM boyd
WHERE userName = b.userName AND alload >= b.alload) <= 3)order by userName ASC,NUM ASC

[qinhl99]

上述还是有问题，当工作量有重复的时候，就不会只取前三条，重复的也应该计入前三条以内