有一个表,里面有三个字段
projectId varchar(50),--项目编号
userName varchar(50),--用户
allload int //工作量
现在要统计每个用户工作量最大的前三个项目,请问怎么下,突然没有思路了
projectId varchar(50),--项目编号
userName varchar(50),--用户
allload int //工作量
现在要统计每个用户工作量最大的前三个项目,请问怎么下,突然没有思路了
解决方案 »
- 麻烦看下这样可以去掉br标签吗?
- asp.net发送邮件问题.
- 关于商品过期问题--有一个条件怎么写??
- 一道面试题目~~~有答案但不知道为什么?高手门告诉下
- javascript里写session的问题 谢谢大家
- asp.net自定控件设计时不能显示图片
- 用asp.net在页面上如何获得web服务器上的文件夹目录?
- 如何转换类型呢??
- 关于水晶报表分组的问题(急,急)
- 高分求购mybatis 的orm的xml 模板 多谢!急用。。。。。
- 如何将带有JPG图片的数据导出到EXCEL中啊.
- ASP .net 中使用水晶报表控件CrystalReportViewer显示报表,求该控件的打印事件.怎么写,我的一点打印就出现空白页.
用程序写个循环,对每个用户都取top 3
楼上是位girl吧?
(
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 boydselect *
into #temp
from boyd
order by userName,alload descselect top 3 * from #temp where userName='you'
select * from #temp
where num <=3
不知道这样是不是符合楼主要求,我没测试过.
NewTable 是表名
(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)
这样试下,别名不能这样用
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)
我没发过提问帖,所以也不知道为什么不能结
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 boyddeclare @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 userNameselect @sql=substring(@con,7,len(@con)-6)select @sql=@sql+' order by username,alload desc'exec(@sql)
不会出现你所说的情况,因为每一人对于特定的项目只可能有一个总工作量(allLoad,我忘记告诉业务规则了,抱歉)
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)
这样则不能正确取到数据
from TB_Chengji as t where name='zxp' order by HeightChines
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)
这样则不能正确取到数据
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
drop table user_project
gocreate table user_project(projectId varchar(20) not null,UserName varchar(50) not null,allload int not null)
goinsert user_project
select '001','a1',10 union all
select '002','a1',10 union all
select '003','a1',12 union all
select '004','a1',11 union all
select '001','a2',10 union all
select '002','a2',13 union all
select '003','a2',12 union all
select '004','a2',11 union all
select '001','a3',10 union all
select '002','a3',15 union all
select '001','a4',10 union all
select '002','a4',13 union all
select '003','a4',12 union all
select '001','a5',10 union all
select '002','a5',13 union all
select '003','a5',12 union all
select '004','a5',11 union all
select '005','a5',15
goselect * from user_project order by 2,1
/*
projectId UserName allload
001 a1 10
002 a1 10
003 a1 12
004 a1 11
001 a2 10
002 a2 13
003 a2 12
004 a2 11
001 a3 10
002 a3 15
001 a4 10
002 a4 13
003 a4 12
001 a5 10
002 a5 13
003 a5 12
004 a5 11
005 a5 15
*/select * from user_project a where projectId in(select top 3 projectId from user_project where a.UserName=UserName order by allload desc) order by 2,1
/*
projectId UserName allload
002 a1 10
003 a1 12
004 a1 11
002 a2 13
003 a2 12
004 a2 11
001 a3 10
002 a3 15
001 a4 10
002 a4 13
003 a4 12
002 a5 13
003 a5 12
005 a5 15
*/
drop table user_project
gocreate table user_project(projectId varchar(20) not null,UserName varchar(50) not null,allload int not null)
goinsert user_project
select '001','a1',10 union all
select '002','a1',10 union all
select '003','a1',12 union all
select '004','a1',11 union all
select '005','a1',11 union all
select '006','a1',13 union all
select '001','a2',10 union all
select '002','a2',13 union all
select '003','a2',12 union all
select '004','a2',11 union all
select '001','a3',10 union all
select '002','a3',15 union all
select '001','a4',10 union all
select '002','a4',13 union all
select '003','a4',12 union all
select '001','a5',10 union all
select '002','a5',13 union all
select '003','a5',12 union all
select '004','a5',11 union all
select '005','a5',15
goselect * from user_project order by 2,1
/*
projectId UserName allload
001 a1 10
002 a1 10
003 a1 12
004 a1 11
005 a1 11
006 a1 13
001 a2 10
002 a2 13
003 a2 12
004 a2 11
001 a3 10
002 a3 15
001 a4 10
002 a4 13
003 a4 12
001 a5 10
002 a5 13
003 a5 12
004 a5 11
005 a5 15
*/select * from user_project a where projectId in(select top 3 projectId from user_project where a.UserName=UserName order by allload desc) order by 2,1
/*
projectId UserName allload
003 a1 12
004 a1 11
006 a1 13
002 a2 13
003 a2 12
004 a2 11
001 a3 10
002 a3 15
001 a4 10
002 a4 13
003 a4 12
002 a5 13
003 a5 12
005 a5 15
*/--重復的算一個
select * from user_project a where projectId in
(
select projectId from user_project where allload in (select distinct top 3 allload from user_project where a.UserName=UserName order by allload desc) and a.UserName=UserName
) order by 2,1
/*
projectId UserName allload
003 a1 12
004 a1 11
005 a1 11
006 a1 13
002 a2 13
003 a2 12
004 a2 11
001 a3 10
002 a3 15
001 a4 10
002 a4 13
003 a4 12
002 a5 13
003 a5 12
005 a5 15
*/