34,576
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int, sc int)
insert into tb
select 1,60 union
select 1,20 union
select 1,10 union
select 2,60 union
select 2,30 union
select 3,30
select id,sum(a.sc) from tb a
group by id
order by 2 desc,(select sc+'' from tb where id=a.id
order by sc desc for xml path('')) desc
/*
id
----------- -----------
2 90
1 90
3 30
(3 row(s) affected)
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-10-26 10:46:26
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([部门ID] int,[学习总分] int)
insert [test]
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
go
with t
as(
select
*,
SUM([学习总分])over(partition by [部门ID]) as counts,
min([学习总分])over(partition by [部门ID]) as maxcount
from
test
)
select
[部门ID] ,
[学习总分]
from t order by counts desc,maxcount desc
----------------结果----------------------------
/*
部门ID 学习总分
----------- -----------
2 60
2 30
1 60
1 20
1 10
3 30
(6 行受影响)
*/
--> 测试数据:#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 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]
----------------结果----------------------------
/*
Deptid SUMscore
4 180
2 180
1 180
3 180
*/
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 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
go
alter function dbo.f_number(@id int)
returns varchar(4000)
as
begin
declare @len int
select @len=max(len(sc)) from tb2
declare @str varchar(4000)
select @str=isnull(@str+',','')+right(replicate('0',@len)+ltrim(sc),@len) from tb2
where Deptid=@id order by sc desc
return @str
end
go
select Deptid,sum(a.sc) sumsc,dbo.f_number(Deptid) rn
from tb2 a
group by Deptid
order by 2 desc,dbo.f_number(Deptid) desc
/*Deptid sumsc rn
----------- ----------- ------------
1 120 90,20,10
3 100 50,50
2 100 50,30,20