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

Question

 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语句解决，

Answer 1

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

Answer 2

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
  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, 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 c.max_sc + SUM(tb2.sc) DESC/*
Deptid  sum_sc   
----------- ----------- 
2           90
1           90
3           30（所影响的行数为 3 行）
*/

Answer 3

--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 a.* 
 from tb2 a INNER JOIN (SELECT deptid,SUM(sc)bsc FROM tb2 GROUP BY deptid) b
 ON a.deptid=b.deptid
 order by bsc DESC  ,a.sc DESC 
 
 /*
 Deptid      sc
 ----------- -----------
 1           60
 2           60
 2           30
 1           20
 1           10
 2           0
 3           30
 
 (7 行受影响)
 
 */

Answer 4

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  
goselect sum(sc) as Sc,DeptID 
from tb2 T
group by DeptID 
order by SUM(SC) DESC,MAX(SC),COUNT(CASE WHEN SC>0 THEN 1 ELSE NULL END) /*Sc          DeptID      
----------- ----------- 
90          2
90          1
30          3（所影响的行数为 3 行）警告: 聚合或其它 SET 操作消除了空值。

Answer 5

楼上不对，如果数据是
    select 1,60 union
   select 1,20 union
   select 1,10 union
   select 2,60 union
   select 2,25 union
   select 2,5 union
   select 3,30 
结果就不对了。上一个帖子我已经贴出来了。

Answer 6

SELECT SUM(SC) Sc,Deptid 
FROM tb2
GROUP BY Deptid
ORDER BY Sc DESC ,SUM(SC) / COUNT(CASE WHEN sc >0 THEN 1 ELSE NULL END) DESC Sc          Deptid
----------- -----------
90          2
90          1
30          3
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)

Answer 7

两个取部门总分
两个取部门的最高分。
然后分别排序就可以了
亏这么多带花花带牌子的
--> 测试数据：#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
*/

Answer 8

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 行）*/

Answer 9

一样不对。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

Answer 10

这个也不对。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

Answer 11

不对。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

Answer 12

如果最高相同，按次高排序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 行）
*/

Answer 13

你这个不对。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

Answer 14

假如了特殊数据测试--> 测试数据：#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
*/

Answer 15

不过需要借助临时表真心不希望你这个用sql 来处理如果是05 还可以写简单点。

Answer 16

你不要用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
*/

Answer 17

--> 测试数据：#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 #tSELECT  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
*/

Answer 18

解决方法如下：先定义一个函数。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=1group by b.DeptName,c.DeptID,a.ExamName,a.PaperID order by sum(GetCountFen) desc,dbo.D_Count(a.PaperID,c.DeptID) desc

Answer 19

方案是行。只是他的函数有BUG而已我已经提了。并已经改了函数了。他没看