34,576
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#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
*/
--> 测试数据:#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
*/
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 行)
*/
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 行)
*/
--> 测试数据:#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
*/