★★★求一条统计排序语句。谢谢

visualcpu 2012-10-26 01:11:38
create table tb2(Deptid int, sc int)
insert into tb2
select 1,60 union
select 1,20 union
select 1,10 union
select 2,60 union
select 2,30 union
select 2,0 union
select 3,30
go
select * from tb2 order by Deptid,sc desc

--要求以DeptID为条件的总值排名,总值相同时，取分值高的deptId排前,上面的实例中部门1和部门2共分值都为90时，就要对比成员分值，成员分值最高的部门就排前。部门2次高分是30，部门1次高分是20，所以部门2应该排前。部门1排后。

select sum(sc) as Sc,DeptID from tb2 group by DeptID order by max(sc) desc

--执行上面一条语句是错误的，应该是DeptID为2的排前 DeptID为1的排后

--希望用一条sql语句解决，
...全文
322 29 打赏 收藏 转发到动态 举报
写回复
用AI写文章
29 条回复
切换为时间正序
请发表友善的回复…
发表回复
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 25 楼 的回复:]

引用 24 楼 的回复:
引用 23 楼 的回复:

解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+r……
[/Quote]
呵呵......
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 26 楼 的回复:]

引用 24 楼 的回复:
引用 23 楼 的回复:

解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+r……
[/Quote]
没有，只是有种被无视的感觉
SQL77 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 的回复:]
引用 23 楼 的回复:

解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+rtrim(a.GetCountF……
[/Quote]
P爱还在意那分分

太不低调了.
SQL77 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 的回复:]
引用 23 楼 的回复:

解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+rtrim(a.GetCountF……
[/Quote]
方案是行。只是他的函数有BUG而已。。。我已经提了。并已经改了函数了。他没看
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 的回复:]

解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+rtrim(a.GetCountFen)+',' from E……
[/Quote]


个人认为这个方案不行。
你可以把我那个查询改成函数来搞

算了，不说了，我觉得我挺多余了，不再回复了。
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
解决方法如下：

先定义一个函数。

create function D_Count(@paperId int,@deptid int )
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+rtrim(a.GetCountFen)+',' from ExamUser a,UserInfo b
where a.UserCode=b.UserCode and a.PaperID=@paperId and b.deptID=@deptid order by a.GetCountFen desc
return @str
end


第二步，在sql语句后面调用即可(注意看最后一个排序字段是函数)

select sum(GetCountFen) as GetCountFen,'0' as CountTime,'0' as ExamTime,b.DeptName,a.ExamName,a.PaperID,c.DeptID from ExamUser a,DeptInfo b,UserInfo c,PaperInfo d where a.UserCode=c.UserCode and b.ID=c.DeptID and d.ID=a.PaperID and a.PaperID=1

group by b.DeptName,c.DeptID,a.ExamName,a.PaperID order by sum(GetCountFen) desc,dbo.D_Count(a.PaperID,c.DeptID) desc
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复 
--> 测试数据：#tb

IF OBJECT_id('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb

GO 

CREATE TABLE #tb([id] int IDENTITY,[Deptid] INT,[score] INT)

INSERT #tb

SELECT 1,50 UNION ALL

SELECT 1,40 UNION ALL

SELECT 1,40 UNION ALL

SELECT 1,30 UNION ALL

SELECT 1,20 UNION ALL

SELECT 2,50 UNION ALL

SELECT 2,40 UNION ALL

SELECT 2,40 UNION ALL

SELECT 2,25 UNION ALL

SELECT 2,25 UNION ALL

SELECT 3,50 UNION ALL

SELECT 3,40 UNION ALL

SELECT 3,40 UNION ALL

SELECT 3,40 UNION ALL

SELECT 3,10 UNION ALL

SELECT 4,170  UNION ALL

SELECT 4,10

GO 

--SELECT *  FROM #tb ORDER BY [Deptid] ,[score] DESC 

IF OBJECT_id('TEMPDB.DBO.#t') IS NOT NULL DROP TABLE #t

GO 

SELECT Deptid,[score],cnt=(SELECT cnt=COUNT(*)+1 FROM #tb WHERE [Deptid]=t.[Deptid] AND ([score]>t.[score] OR([score]=t.[score] AND id<t.id)))

INTO #t

FROM #tb AS t 

--SELECT * FROM #t



SELECT  a.Deptid,a.[SUMscore] FROM 

(

	SELECT Deptid,SUM([score]) AS [SUMscore],MAX([score]) AS [MAXscore]

	FROM #tb AS t GROUP BY Deptid

)AS a

LEFT JOIN 

(

	SELECT Deptid,MAX([score]) AS [MAXscore] FROM #t AS t

	WHERE  EXISTS(SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND [score]>t.[score]

	AND NOT EXISTS (SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND  [score]=t.[score] AND [cnt]=t.[cnt]))

	GROUP BY [Deptid]

)AS b

ON a.Deptid=b.Deptid

ORDER BY a.[SUMscore] DESC,a.[MAXscore] DESC ,b.[MAXscore] DESC



----------------结果----------------------------

/* 

Deptid	SUMscore

4	180

3	180

1	180

2	180

*/
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 的回复:]

引用 16 楼 的回复:

如果最高相同，按次高排序
SQL code

IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2(Deptid INT,sc INT)

insert into tb2
select 1,90 union
select 1,20 union
select 1,1……
[/Quote]

你不要用union 啊
用union all



--> 测试数据：#tb
IF OBJECT_id('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] int IDENTITY,[Deptid] INT,[score] INT)
INSERT #tb
SELECT 1 , 90
UNION ALL
SELECT 1 , 20
UNION ALL
SELECT 1 , 10
UNION ALL
SELECT 2 , 50
UNION ALL
SELECT 2 , 30
UNION ALL
SELECT 2 , 20
UNION ALL
SELECT 3 , 50
UNION ALL
SELECT 3 , 50
UNION ALL
SELECT 3 , 0
GO

IF OBJECT_id('TEMPDB.DBO.#t') IS NOT NULL DROP TABLE #t
GO
SELECT Deptid,[score],cnt=(SELECT cnt=COUNT(*)+1 FROM #tb WHERE [Deptid]=t.[Deptid] AND ([score]>t.[score] OR([score]=t.[score] AND id<t.id)))
INTO #t
FROM #tb AS t

SELECT a.Deptid,[SUMscore] FROM
(
SELECT Deptid,SUM([score]) AS [SUMscore]
FROM #tb AS t GROUP BY Deptid
)AS a
JOIN
(
SELECT Deptid,MAX([score]) AS [MAXscore] FROM #t AS t
WHERE EXISTS(SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND [score]>t.[score]
AND NOT EXISTS (SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND [score]=t.[score] AND [cnt]=t.[cnt]))
GROUP BY [Deptid]
)AS b
ON a.Deptid=b.Deptid
ORDER BY [SUMscore] DESC,[MAXscore] DESC

----------------结果----------------------------
/*
Deptid SUMscore
1 120
3 100
2 100
*/
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
不过需要借助临时表

真心不希望你这个用sql 来处理

如果是05 还可以写简单点。
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 的回复:]

如果最高相同，按次高排序
SQL code

IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2(Deptid INT,sc INT)

insert into tb2
select 1,90 union
select 1,20 union
select 1,10 union
select 2,……
[/Quote]

以你这个语句是错的，正确的排序为部门1 ，部门3，部门2
因为部门1 共120分。部门3共100，但他第二高分为50 部门2虽然共100分，但他第二高分为30
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
假如了特殊数据测试




--> 测试数据：#tb

IF OBJECT_id('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb

GO 

CREATE TABLE #tb([id] int IDENTITY,[Deptid] INT,[score] INT)

INSERT #tb

SELECT 1,60 UNION ALL

SELECT 1,30 UNION ALL

SELECT 1,20 UNION ALL

SELECT 1,10 UNION ALL

SELECT 2,60 UNION ALL

SELECT 2,30 UNION ALL

SELECT 2,30 UNION ALL

SELECT 3,30

GO 

IF OBJECT_id('TEMPDB.DBO.#t') IS NOT NULL DROP TABLE #t

GO 

SELECT Deptid,[score],cnt=(SELECT cnt=COUNT(*)+1 FROM #tb WHERE [Deptid]=t.[Deptid] AND ([score]>t.[score] OR([score]=t.[score] AND id<t.id)))

INTO #t

FROM #tb AS t 



SELECT  a.Deptid,[SUMscore] FROM 

(

	SELECT Deptid,SUM([score]) AS [SUMscore] 

	FROM #tb AS t GROUP BY Deptid

)AS a

JOIN 

(

	SELECT Deptid,MAX([score]) AS [MAXscore] FROM #t AS t

	WHERE  EXISTS(SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND [score]>t.[score]

	AND NOT EXISTS (SELECT 1 FROM #t WHERE [Deptid]<>t.[Deptid] AND  [score]=t.[score] AND [cnt]=t.[cnt]))

	GROUP BY [Deptid]

)AS b

ON a.Deptid=b.Deptid

ORDER BY [SUMscore] DESC,[MAXscore] DESC 



----------------结果----------------------------

/* 

Deptid	SUMscore

2	120

1	120

3	30

*/
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 的回复:]

如果最高相同，按次高排序
SQL code

IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2(Deptid INT,sc INT)

insert into tb2
select 1,90 union
select 1,20 union
select 1,10 union
select 2,……
[/Quote]
你这个不对。

drop table tb2
create table tb2(id int,Deptid int, sc int)
insert into tb2
select 8,1,90 union
select 7,1,20 union
select 10,1,10 union
select 5,2,50 union
select 4,2,30 union
select 30,2,20 union
select 2,3,50 union
select 11,3,50
go
极品老土豆 2012-10-26
  • 打赏
  • 举报
 回复
如果最高相同，按次高排序


IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2

GO 

CREATE TABLE tb2(Deptid INT,sc INT)



insert into tb2

 select 1,90 union

  select 1,20 union

  select 1,10 union

  select 2,50 union

  select 2,30 union

  select 2,20 union

  select 3,50 union

  select 3,50  















SELECT tb2.Deptid, SUM(tb2.sc) aS sum_sc

FROM tb2 INNER JOIN

          (SELECT b.Deptid, MaX(sc) AS max_sc

         FROM tb2 as b

         WHERE EXISTS

                   (SELECT TOP 1 *

                  FROM tb2

                  WHERE b.Deptid <> Deptid AND sc > b.sc)

         GROUP BY b.Deptid) c ON c.Deptid = tb2.Deptid

GROUP BY tb2.Deptid, c.max_sc

ORDER BY SUM(tb2.sc) DESC,c.max_sc desc 



/*

Deptid      sum_sc      

----------- ----------- 

1           240

2           220

3           80



（所影响的行数为 3 行）

*/

[Quote=引用 12 楼 的回复:]
引用 9 楼 的回复:

两个取部门总分
两个取部门的最高分。
然后分别排序就可以了
亏这么多带花花带牌子的
SQL code
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] INT,[score] INT)
INSERT #tb
……
[/Quote]
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 的回复:]

SQL code

IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2(Deptid INT,sc INT)


insert into tb2
select 1,60 union
select 1,20 union
select 1,10 union
select 2,60 union
……
[/Quote]

不对。

drop table tb2
create table tb2(Deptid int, sc int)
insert into tb2
select 1,90 union
select 1,20 union
select 1,10 union
select 2,50 union
select 2,30 union
select 2,20 union
select 3,50 union
select 3,50 union
select 3,0
go
极品老土豆 2012-10-26
  • 打赏
  • 举报
 回复
日的，贴错结果了
[Quote=引用 11 楼 的回复:]
SQL code

IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
GO
CREATE TABLE tb2(Deptid INT,sc INT)


insert into tb2
select 1,60 union
select 1,20 union
select 1,10 union
select 2,6……
[/Quote]
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 的回复:]

SQL code

if(object_id('tb2') is not null) drop table tb2
create table tb2(Deptid int, sc int)
insert into tb2
select 1,60 union
select 1,20 union
select 1,10 union
select 2,60 union
sel……
[/Quote]

这个也不对。

drop table tb2
create table tb2(Deptid int, sc int)
insert into tb2
select 1,90 union
select 1,20 union
select 1,10 union
select 2,50 union
select 2,30 union
select 2,20 union
select 3,50 union
select 3,50
go
visualcpu 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]

两个取部门总分
两个取部门的最高分。
然后分别排序就可以了
亏这么多带花花带牌子的
SQL code
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] INT,[score] INT)
INSERT #tb
SELECT 1,60 UNION ……
[/Quote]


一样不对。

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([tid] int,[ID] INT,[score] INT)
INSERT #tb
select 1,1,90 union
select 2,1,20 union
select 3,1,10 union
select 4,2,50 union
select 5,2,30 union
select 6,2,20 union
select 7,3,50 union
select 8,3,50
go
极品老土豆 2012-10-26
  • 打赏
  • 举报
 回复 


IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2

GO 

CREATE TABLE tb2(Deptid INT,sc INT)





  insert into tb2

  select 1,60 union

  select 1,20 union

  select 1,10 union

  select 2,60 union

  select 2,30 union

  select 2,0 union

  select 3,30  

go









SELECT tb2.Deptid, SUM(tb2.sc) aS sum_sc

FROM tb2 INNER JOIN

          (SELECT b.Deptid, Min(sc) AS max_sc

         FROM tb2 as b

         WHERE EXISTS

                   (SELECT TOP 1 *

                  FROM tb2

                  WHERE b.Deptid <> Deptid AND sc > b.sc)

         GROUP BY b.Deptid) c ON c.Deptid = tb2.Deptid

GROUP BY tb2.Deptid, c.max_sc

ORDER BY c.max_sc + SUM(tb2.sc) DESC



/*

Deptid      sum_sc      

----------- ----------- 

1           90

2           90

3           30



（所影响的行数为 3 行）



*/
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]

两个取部门总分
两个取部门的最高分。
然后分别排序就可以了
亏这么多带花花带牌子的
SQL code
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([ID] INT,[score] INT)
INSERT #tb
SELECT 1,60 UNION ……
[/Quote]

两个子查询一个取部门总分
另外一个取除了相同分外的最高分
汤姆克鲁斯 2012-10-26
  • 打赏
  • 举报
 回复
两个取部门总分
两个取部门的最高分。
然后分别排序就可以了
亏这么多带花花带牌子的
--> 测试数据：#tb

IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb

GO 

CREATE TABLE #tb([ID] INT,[score] INT)

INSERT #tb

SELECT 1,60 UNION ALL

SELECT 1,20 UNION ALL

SELECT 1,10 UNION ALL

SELECT 2,60 UNION ALL

SELECT 2,30 UNION ALL

SELECT 3,30

--------------开始查询--------------------------





SELECT  a.id,[SUMscore] FROM 

(

	SELECT id,SUM([score]) AS [SUMscore] 

	FROM #tb AS t GROUP BY id

)AS a

JOIN 

(

	SELECT id,MAX([score]) [MAXscore] FROM #tb AS t 

	WHERE EXISTS(SELECT 1 FROM #tb WHERE [ID]<>t.[ID] AND [score]>t.[score])

	GROUP BY [ID]

)AS b

ON a.id=b.id

ORDER BY [SUMscore] DESC,[MAXscore] DESC 



----------------结果----------------------------

/* 

id	SUMscore

2	90

1	90

3	30

*/
加载更多回复（8）
mysql语句统计总数_SQL一条语句统计记录总数及各状态数
SQL一条语句统计记录总数及各状态数方法一、代码如下:SELECT SUM(正确数)+SUM(错误数) AS 总记录数,SUM(正确数),SUM(错误数)FROM (SELECT COUNT(1) 正确数,0 错误数FROM TBWHERE STATUS=1UNION ALLSELECT 0 正确数,...
SQL进行排序、分组、统计
有时你需要的可能是一个简单的排序，但是通常你需要做更多，你需要分组来进行分析和统计。幸运的是，SQL提供了大量语句和操作来进行排序，分组和摘要。下面的一些技巧将会帮助你识别什么时候排序，什么时候分组，...
mysql分组排列的查询语句_数据的排序与分组语句_MySQL
bitsCN.com数据的排序与分组语句使用SQL...在数据库的实际应用中，有时需要对查询的数据进行统计和分组操作，这就需要了解SQL语句的聚合函数和GROUP BY子句的使用。有些时候开发人员或者用户还希望对分组后的结果做...
Oracle中实现分组后按时间排序取第一条
②分组排序后取第一条的效果： 二、sql语句 2.1、基础语句 select t.* from (select a.*, row_number() over(partition by 需要分组的字段 order by 排序的字段 desc) rw from 表 a) t where t.rw = 1 ...
SQL查询语句（查询结果排序）
SQL查询语句（查询结果排序） 1.以指定次序对查询结果排序 //显示部门10中的员工名字、职位和工资，并按照工资升序排序 select enamel, job, sal from emp where deptno = 10 order by sal asc //显示部门10中的...
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章