求大神帮忙修改下SQL语句！

daiqing1988 2013-08-30 11:21:14

数据表如下：
ScoreId BRNO PlanId USID TotalScore GroupId SubmitDate WithTime IdPlan QueryType USNM NamePlan ClassName
327 1 9 S0001 10 8月26日 1481330 13 5 学生1 a竞赛 201301班
328 1 9 S0002 5 8月26日 1469268 13 5 张丽 a竞赛 201301班
329 1 9 S0003 15 8月28日 1623584 13 5 张飞 a竞赛 201301班
330 1 9 s0004 0 7月18日 30 13 5 学生4 a竞赛 201301班
331 1 9 s0005 48 8月9日 20 13 5 212 a竞赛 201301班
332 1 9 s0006 48 8月9日 20 13 5 学生6 a竞赛 201301班
349 1 5 s0003 1.7 8月28日 109 13 6 张飞 a竞赛 201301班
350 1 5 s0002 3.16 8月28日 252 13 6 张丽 a竞赛 201301班
351 1 5 S0001 4.76 8月28日 260 13 6 学生1 a竞赛 201301班
352 1 5 s0001 0 8月28日 1800 18 6 学生1 b竞赛金融201301班

我现在写的一个SQL语句如下：
select m.* ,a.人数 from (select row_number() over(order by sum(TotalScore) desc )as 名次, USID as 账户 , WithTime AS 用时 ,sum(TotalScore) 总分,NamePlan as 竞赛名称 from V_Score_Name where BRNO=1 group by USID ,WithTime,NamePlan) m ,(select count(*)as 人数 from V_Score_Name where BRNO=1 ) a WHERE 账户='S0001' order by 名次

查询结果如下：
名次账户用时总分竞赛名称人数
4 S0001 1481330 10 a竞赛 10
6 S0001 260 4.76 a竞赛 10
10 s0001 1800 0 b竞赛 10

我需要查询的结果：
名次账户用时总分竞赛名称人数
？ S0001 1481590 14.76 a竞赛 10
1 s0001 1800 0 b竞赛 1

不知道说的是否清楚，总之我排序的条件是按总分数，大则排名靠前，如果分数一样则总用时短的排名靠前！

...全文

200 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

daiqing1988 2013-08-30

打赏
举报

比较接近了,但是应该不可能出现名称一样的情况,因为就算"总分"一样,但是又根据"用时"排序了,"用时"一样的可能性比较低.还有就是统计的人数好像有点问题,我这里有10条数据,去掉另一个竞赛的用户,应该是9人才对,名次也应该是在此9人中的排名,不知是否说清楚了. 我想要的结果为(以账户S0001作为查询条件): 名次账户用时总分竞赛名称人数 ? S0001 1481590 14.76 a竞赛 9 1 s0001 1800 0 b竞赛 1

Shawn 2013-08-30

打赏
举报

引用 2 楼 daiqing1988 的回复:

先谢谢楼上的百忙之中能抽空帮我看这个问题，我的意思其实就是先根据用户名和竞赛名称统计一个总分和总用时，然后再以总分（越高排名越高），用时（越小排名越高）排名！

--下面可是你要的结果？

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [ScoreId] int, [BRNO] int, [PlanId] int, [USID] varchar(100), [TotalScore] DECIMAL(10,2), [SubmitDate] varchar(100), [WithTime] bigint, [IdPlan] int, [QueryType] int, [USNM] varchar(100), [NamePlan] varchar(100), [ClassName] varchar(100));
insert #temp
select '327','1','9','S0001','10','8月26日','1481330','13','5','学生1','a竞赛','201301班' union all
select '328','1','9','S0002','5','8月26日','1469268','13','5','张丽','a竞赛','201301班' union all
select '329','1','9','S0003','15','8月28日','1623584','13','5','张飞','a竞赛','201301班' union all
select '330','1','9','s0004','0','7月18日','30','13','5','学生4','a竞赛','201301班' union all
select '331','1','9','s0005','48','8月9日','20','13','5','212','a竞赛','201301班' union all
select '332','1','9','s0006','48','8月9日','20','13','5','学生6','a竞赛','201301班' union all
select '349','1','5','s0003','1.7','8月28日','109','13','6','张飞','a竞赛','201301班' union all
select '350','1','5','s0002','3.16','8月28日','252','13','6','张丽','a竞赛','201301班' union all
select '351','1','5','S0001','4.76','8月28日','260','13','6','学生1','a竞赛','201301班' union all
select '352','1','5','s0001','0','8月28日','1800','18','6','学生1','b竞赛','金融201301班' 
 
--SQL:
SELECT  m.*
FROM   ( SELECT    RANK() OVER (PARTITION BY NamePlan ORDER BY SUM(TotalScore) DESC, SUM(WithTime)) AS 名次 ,
                    USID AS 账户 ,
                    SUM(WithTime) AS 用时 ,
                    SUM(TotalScore) 总分 ,
                    NamePlan AS 竞赛名称,
                    COUNT(*) OVER(PARTITION BY NamePlan) AS 人数
          FROM      #temp
          WHERE     BRNO = 1
          GROUP BY  USID ,
                    NamePlan
        ) m
ORDER BY 竞赛名称, 名次
/*
名次	账户	用时	总分	竞赛名称	人数
1	s0005	20	48.00	a竞赛	6
1	s0006	20	48.00	a竞赛	6
3	S0003	1623693	16.70	a竞赛	6
4	S0001	1481590	14.76	a竞赛	6
5	s0002	1469520	8.16	a竞赛	6
6	s0004	30	0.00	a竞赛	6
1	s0001	1800	0.00	b竞赛	1
*/

daiqing1988 2013-08-30

打赏
举报

先谢谢楼上的百忙之中能抽空帮我看这个问题，我的意思其实就是先根据用户名和竞赛名称统计一个总分和总用时，然后再以总分（越高排名越高），用时（越小排名越高）排名！

Shawn 2013-08-30

打赏
举报

--楼主说的不是很清楚

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [ScoreId] int, [BRNO] int, [PlanId] int, [USID] varchar(100), [TotalScore] DECIMAL(10,2), [SubmitDate] varchar(100), [WithTime] bigint, [IdPlan] int, [QueryType] int, [USNM] varchar(100), [NamePlan] varchar(100), [ClassName] varchar(100));
insert #temp
select '327','1','9','S0001','10','8月26日','1481330','13','5','学生1','a竞赛','201301班' union all
select '328','1','9','S0002','5','8月26日','1469268','13','5','张丽','a竞赛','201301班' union all
select '329','1','9','S0003','15','8月28日','1623584','13','5','张飞','a竞赛','201301班' union all
select '330','1','9','s0004','0','7月18日','30','13','5','学生4','a竞赛','201301班' union all
select '331','1','9','s0005','48','8月9日','20','13','5','212','a竞赛','201301班' union all
select '332','1','9','s0006','48','8月9日','20','13','5','学生6','a竞赛','201301班' union all
select '349','1','5','s0003','1.7','8月28日','109','13','6','张飞','a竞赛','201301班' union all
select '350','1','5','s0002','3.16','8月28日','252','13','6','张丽','a竞赛','201301班' union all
select '351','1','5','S0001','4.76','8月28日','260','13','6','学生1','a竞赛','201301班' union all
select '352','1','5','s0001','0','8月28日','1800','18','6','学生1','b竞赛','金融201301班' 

--SQL:
SELECT  m.*
FROM   ( SELECT    ROW_NUMBER() OVER (PARTITION BY NamePlan ORDER BY SUM(TotalScore) DESC, SUM(WithTime)) AS 名次 ,
                    USID AS 账户 ,
                    SUM(WithTime) AS 用时 ,
                    SUM(TotalScore) 总分 ,
                    NamePlan AS 竞赛名称,
					COUNT(*) OVER(PARTITION BY NamePlan) AS 人数
          FROM      #temp
          WHERE     BRNO = 1
          GROUP BY  USID ,
                    NamePlan
        ) m
WHERE   账户 = 'S0001'
ORDER BY 名次
/*
名次	账户	用时	总分	竞赛名称	人数
1	s0001	1800	0.00	b竞赛	1
4	S0001	1481590	14.76	a竞赛	6
*/

xxfvba 2013-08-30

打赏
举报

with test([ScoreId],BRNO,[PlanId],USID,TotalScore,SubmitDate,WithTime,IdPlan,QueryType,USNM,NamePlan,ClassName) as (select '327','1','9','S0001',10,'8月26日',1481330,'13','5','学生1','a竞赛','201301班' union all select '328','1','9','S0002',5,'8月26日',1469268,'13','5','张丽','a竞赛','201301班' union all select '329','1','9','S0003',15,'8月28日',1623584,'13','5','张飞','a竞赛','201301班' union all select '330','1','9','s0004',0,'7月18日',30,'13','5','学生4','a竞赛','201301班' union all select '331','1','9','s0005',48,'8月9日',20,'13','5','212','a竞赛','201301班' union all select '332','1','9','s0006',48,'8月9日',20,'13','5','学生6','a竞赛','201301班' union all select '349','1','5','s0003',1.7,'8月28日',109,'13','6','张飞','a竞赛','201301班' union all select '350','1','5','s0002',3.16,'8月28日',252,'13','6','张丽','a竞赛','201301班' union all select '351','1','5','S0001',4.76,'8月28日',260,'13','6','学生1','a竞赛','201301班' union all select '352','1','5','S0001',0,'8月28日',1800,'18','6','学生1','b竞赛','金融201301班') SELECT ROW_NUMBER() OVER (PARTITION BY USId ORDER BY SUM(TotalScore) DESC, SUM(WithTime)) AS 名次 , USID AS 账户,SUM(WithTime) AS 用时 ,SUM(TotalScore) 总分 ,NamePlan AS 竞赛名称,(select count(*) from test b where a.Nameplan=b.Nameplan) AS 人数 FROM test a WHERE BRNO = 1 and USId='S0001'GROUP BY USID ,NamePlan order by 名次,usid /* 1 S0001 1481590 14.76 a竞赛 9 2 S0001 1800 0.00 b竞赛 1 */

daiqing1988 2013-08-30

打赏
举报

呵呵是啊连我自己都看错了，后面发现不对，果断把数据整理了一下，再测试，OK的！总之多谢楼上了我纠结了1上午没纠结出来，还是你帮我搞出来的！

Shawn 2013-08-30

打赏
举报

引用 6 楼 daiqing1988 的回复:

呵呵？是应为这结果是我预想的结果不知道排名第几已经解决了结贴了！多谢了大神！100分奉上！

呵呵，是因为这结果，你是想要的结果。也是因为你的数据，是随便造的，有时看不出你真正想要的结果。

daiqing1988 2013-08-30

打赏
举报

其实中间我有点搞错了，人数其实是6 你第二次发的统计“总人数”是对的我自己修改了下现在查询出结果是我想要的结果了! 1 s0001 44 0 b竞赛 1 3 S0001 122 10 a竞赛 6

daiqing1988 2013-08-30

打赏
举报

呵呵？是应为这结果是我预想的结果不知道排名第几已经解决了结贴了！多谢了大神！100分奉上！

Shawn 2013-08-30

打赏
举报

引用 4 楼 daiqing1988 的回复:

比较接近了,但是应该不可能出现名称一样的情况,因为就算"总分"一样,但是又根据"用时"排序了,"用时"一样的可能性比较低.还有就是统计的人数好像有点问题,我这里有10条数据,去掉另一个竞赛的用户,应该是9人才对,名次也应该是在此9人中的排名,不知是否说清楚了. 我想要的结果为(以账户S0001作为查询条件): 名次账户用时总分竞赛名称人数 ? S0001 1481590 14.76 a竞赛 9 1 s0001 1800 0 b竞赛 1

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [ScoreId] int, [BRNO] int, [PlanId] int, [USID] varchar(100), [TotalScore] DECIMAL(10,2), [SubmitDate] varchar(100), [WithTime] bigint, [IdPlan] int, [QueryType] int, [USNM] varchar(100), [NamePlan] varchar(100), [ClassName] varchar(100));
insert #temp
select '327','1','9','S0001','10','8月26日','1481330','13','5','学生1','a竞赛','201301班' union all
select '328','1','9','S0002','5','8月26日','1469268','13','5','张丽','a竞赛','201301班' union all
select '329','1','9','S0003','15','8月28日','1623584','13','5','张飞','a竞赛','201301班' union all
select '330','1','9','s0004','0','7月18日','30','13','5','学生4','a竞赛','201301班' union all
select '331','1','9','s0005','48','8月9日','20','13','5','212','a竞赛','201301班' union all
select '332','1','9','s0006','48','8月9日','20','13','5','学生6','a竞赛','201301班' union all
select '349','1','5','s0003','1.7','8月28日','109','13','6','张飞','a竞赛','201301班' union all
select '350','1','5','s0002','3.16','8月28日','252','13','6','张丽','a竞赛','201301班' union all
select '351','1','5','S0001','4.76','8月28日','260','13','6','学生1','a竞赛','201301班' union all
select '352','1','5','s0001','0','8月28日','1800','18','6','学生1','b竞赛','金融201301班' 

--结果里为什么有个？号呢？
--SQL:
SELECT  m.*
FROM   ( SELECT    ROW_NUMBER() OVER (PARTITION BY NamePlan ORDER BY SUM(TotalScore) DESC, SUM(WithTime)) AS 名次 ,
                    USID AS 账户 ,
                    SUM(WithTime) AS 用时 ,
                    SUM(TotalScore) 总分 ,
                    NamePlan AS 竞赛名称,
					(SELECT COUNT(*) FROM #temp x WHERE x.NamePlan=a.NamePlan) AS 人数
          FROM      #temp a
          WHERE     BRNO = 1
          GROUP BY  USID ,
                    NamePlan
        ) m    
WHERE 账户='S0001'
ORDER BY 竞赛名称, 名次
/*
名次	账户	用时	总分	竞赛名称	人数
4	S0001	1481590	14.76	a竞赛	9
1	s0001	1800	0.00	b竞赛	1
*/

Excel根据表格，生成sql语句 ，生成建表语句，自行到数据库中执行，需要excel启用宏，按规则填充表格后可生成表格目录及生成sqlserver语句。

总结大量sql操作语句，适合初学者晋级学习，及详细的讲解一步步成为sql大神，汇总sql高频操作和实例详解。

课程概述：本课程旨在0基础学习SQL注入技术 , 从系统安装=>数据库安装=>数据库基础语法=>SQL注入漏洞解析等多方面 , 深入探讨SQL注入漏洞技术，并提供有效的防御策略。通过理论与实践相结合的方式，学员...

SSM 框架，详细的配置文件和sql语句。

今天在更改字段类型的SQL出现了错误，还好有大神朋友帮忙指正，大神看一眼SQL就发现了问题，我还在被SQL日志带的转圈圈，看来我和大神之间的距离不是肉眼可及的。特意回顾了其他常见但是容易忽视而出错的SQL语句。 ...