22,206
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([运动员编号] INT,[对别] VARCHAR(1),[预赛成绩] INT)
INSERT #tb
SELECT 1,'a',50 UNION ALL
SELECT 2,'a',60 UNION ALL
SELECT 3,'a',70 UNION ALL
SELECT 4,'a',40 UNION ALL
SELECT 5,'a',10 UNION ALL
SELECT 6,'a',70 UNION ALL
SELECT 7,'b',10 UNION ALL
SELECT 8,'b',20 UNION ALL
SELECT 9,'b',80 UNION ALL
SELECT 10,'b',10 UNION ALL
SELECT 11,'b',60 UNION ALL
SELECT 12,'b',90
--------------开始查询--------------------------
--分组
;WITH cte AS (
SELECT *,row_id=ROW_NUMBER()OVER(PARTITION BY [对别] ORDER BY [运动员编号]) FROM #tb
)
SELECT *,组=(ROW_NUMBER()OVER( ORDER BY row_id ,[对别])+5)/6 FROM cte
--分赛道
;WITH cte AS (
SELECT *,row_id=ROW_NUMBER()OVER(PARTITION BY [对别] ORDER BY [运动员编号]) FROM #tb
)
, cte2 AS
(
SELECT *,组=(ROW_NUMBER()OVER( ORDER BY row_id ,[对别])+5)/6 FROM cte
)
,cte3 AS
(
SELECT *,row_id2=ROW_NUMBER()OVER(PARTITION BY 组 ORDER BY [预赛成绩] DESC ) FROM cte2
)
/*既然给1/2名指定了赛道,不妨给每个人都指定一个赛道,呵呵*/
SELECT *,赛道=CASE row_id2 WHEN 1 THEN 3
WHEN 2 THEN 5
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 5 THEN 4
WHEN 6 THEN 6
END
FROM cte3