找出每个班级里面任意3门学科总分学科超过290分的学生前3名，按总分排序

前段时间接到国内一家潮流鞋品网购平台的大数据工程师的面试，第一轮面试对方工程师提问了不少SQL的例子考我。虽然写了这么多年SQL，但是居然当中有道题给难住了，电话里面答不出来，事后自己才解答了出来。我觉得也确实好久没有遇到这种难的SQL例子，这里写下博文记录一下。

场景：现在有张成绩表（score），字段有班级（class），学生（student），成绩（score），期末考试一共有7门学科，期末考试单科满分是100分，现在要求找出每个班级里面任意3门学科总分学科超过290分的学生前3名，要求前3名的学生的3科学科成绩都按分数高到低排序。

像这种题，抛开性能不谈，其实可以有多种T-SQL解法，我脑海里面想到的思路：子查询，关联查询，开窗聚合函数和CROSS APPLY子句（当然这个是SQL SERVER独有的）

先创建score表，然后把学生成绩插入到表中（这里我为了造出成绩，我用ABS(CHECKSUM(NEWID()))%10+91）

  1 CREATE TABLE dbo.score ( [class] nvarchar(10), [student] nvarchar(10), [subject_name] nvarchar(10), [score] decimal(4,1) )
  2 INSERT INTO dbo.score
  3 VALUES
  4 ( N'高一4班', N'马云', N'语文', 92.0 ), 
  5 ( N'高一4班', N'马云', N'数学', 98.0 ), 
  6 ( N'高一4班', N'马云', N'英语', 94.0 ), 
  7 ( N'高一4班', N'马云', N'政治', 95.0 ), 
  8 ( N'高一4班', N'马云', N'历史', 92.0 ), 
  9 ( N'高一4班', N'马云', N'物理', 97.0 ), 
 10 ( N'高一4班', N'马云', N'化学', 100.0 ), 
 11 ( N'高一4班', N'马云', N'地理', 100.0 ), 
 12 ( N'高一4班', N'马云', N'生物', 93.0 ), 
 13 ( N'高一4班', N'马化腾', N'语文', 98.0 ), 
 14 ( N'高一4班', N'马化腾', N'数学', 99.0 ), 
 15 ( N'高一4班', N'马化腾', N'英语', 93.0 ), 
 16 ( N'高一4班', N'马化腾', N'政治', 100.0 ), 
 17 ( N'高一4班', N'马化腾', N'历史', 95.0 ), 
 18 ( N'高一4班', N'马化腾', N'物理', 96.0 ), 
 19 ( N'高一4班', N'马化腾', N'化学', 94.0 ), 
 20 ( N'高一4班', N'马化腾', N'地理', 95.0 ), 
 21 ( N'高一4班', N'马化腾', N'生物', 98.0 ), 
 22 ( N'高一4班', N'李彦宏', N'语文', 93.0 ), 
 23 ( N'高一4班', N'李彦宏', N'数学', 96.0 ), 
 24 ( N'高一4班', N'李彦宏', N'英语', 92.0 ), 
 25 ( N'高一4班', N'李彦宏', N'政治', 92.0 ), 
 26 ( N'高一4班', N'李彦宏', N'历史', 100.0 ), 
 27 ( N'高一4班', N'李彦宏', N'物理', 94.0 ), 
 28 ( N'高一4班', N'李彦宏', N'化学', 97.0 ), 
 29 ( N'高一4班', N'李彦宏', N'地理', 92.0 ), 
 30 ( N'高一4班', N'李彦宏', N'生物', 99.0 ), 
 31 ( N'高一4班', N'王思聪', N'语文', 98.0 ), 
 32 ( N'高一4班', N'王思聪', N'数学', 95.0 ), 
 33 ( N'高一4班', N'王思聪', N'英语', 92.0 ), 
 34 ( N'高一4班', N'王思聪', N'政治', 100.0 ), 
 35 ( N'高一4班', N'王思聪', N'历史', 94.0 ), 
 36 ( N'高一4班', N'王思聪', N'物理', 97.0 ), 
 37 ( N'高一4班', N'王思聪', N'化学', 96.0 ), 
 38 ( N'高一4班', N'王思聪', N'地理', 98.0 ), 
 39 ( N'高一4班', N'王思聪', N'生物', 96.0 ), 
 40 ( N'高一4班', N'张一鸣', N'语文', 97.0 ), 
 41 ( N'高一4班', N'张一鸣', N'数学', 99.0 ), 
 42 ( N'高一4班', N'张一鸣', N'英语', 96.0 ), 
 43 ( N'高一4班', N'张一鸣', N'政治', 100.0 ), 
 44 ( N'高一4班', N'张一鸣', N'历史', 97.0 ), 
 45 ( N'高一4班', N'张一鸣', N'物理', 95.0 ), 
 46 ( N'高一4班', N'张一鸣', N'化学', 91.0 ), 
 47 ( N'高一4班', N'张一鸣', N'地理', 94.0 ), 
 48 ( N'高一4班', N'张一鸣', N'生物', 98.0 ), 
 49 ( N'高一4班', N'王兴', N'语文', 100.0 ), 
 50 ( N'高一4班', N'王兴', N'数学', 95.0 ), 
 51 ( N'高一4班', N'王兴', N'英语', 96.0 ), 
 52 ( N'高一4班', N'王兴', N'政治', 92.0 ), 
 53 ( N'高一4班', N'王兴', N'历史', 99.0 ), 
 54 ( N'高一4班', N'王兴', N'物理', 97.0 ), 
 55 ( N'高一4班', N'王兴', N'化学', 100.0 ), 
 56 ( N'高一4班', N'王兴', N'地理', 95.0 ), 
 57 ( N'高一4班', N'王兴', N'生物', 98.0 ), 
 58 ( N'高一4班', N'刘强东', N'语文', 93.0 ), 
 59 ( N'高一4班', N'刘强东', N'数学', 96.0 ), 
 60 ( N'高一4班', N'刘强东', N'英语', 96.0 ), 
 61 ( N'高一4班', N'刘强东', N'政治', 98.0 ), 
 62 ( N'高一4班', N'刘强东', N'历史', 96.0 ), 
 63 ( N'高一4班', N'刘强东', N'物理', 97.0 ), 
 64 ( N'高一4班', N'刘强东', N'化学', 97.0 ), 
 65 ( N'高一4班', N'刘强东', N'地理', 94.0 ), 
 66 ( N'高一4班', N'刘强东', N'生物', 98.0 ), 
 67 ( N'高一4班', N'黄铮', N'语文', 94.0 ), 
 68 ( N'高一4班', N'黄铮', N'数学', 91.0 ), 
 69 ( N'高一4班', N'黄铮', N'英语', 92.0 ), 
 70 ( N'高一4班', N'黄铮', N'政治', 100.0 ), 
 71 ( N'高一4班', N'黄铮', N'历史', 92.0 ), 
 72 ( N'高一4班', N'黄铮', N'物理', 93.0 ), 
 73 ( N'高一4班', N'黄铮', N'化学', 91.0 ), 
 74 ( N'高一4班', N'黄铮', N'地理', 97.0 ), 
 75 ( N'高一4班', N'黄铮', N'生物', 100.0 ), 
 76 ( N'高一4班', N'丁磊', N'语文', 98.0 ), 
 77 ( N'高一4班', N'丁磊', N'数学', 95.0 ), 
 78 ( N'高一4班', N'丁磊', N'英语', 95.0 ), 
 79 ( N'高一4班', N'丁磊', N'政治', 95.0 ), 
 80 ( N'高一4班', N'丁磊', N'历史', 91.0 ), 
 81 ( N'高一4班', N'丁磊', N'物理', 96.0 ), 
 82 ( N'高一4班', N'丁磊', N'化学', 100.0 ), 
 83 ( N'高一4班', N'丁磊', N'地理', 94.0 ), 
 84 ( N'高一4班', N'丁磊', N'生物', 98.0 ), 
 85 ( N'高一4班', N'雷军', N'语文', 100.0 ), 
 86 ( N'高一4班', N'雷军', N'数学', 92.0 ), 
 87 ( N'高一4班', N'雷军', N'英语', 98.0 ), 
 88 ( N'高一4班', N'雷军', N'政治', 95.0 ), 
 89 ( N'高一4班', N'雷军', N'历史', 94.0 ), 
 90 ( N'高一4班', N'雷军', N'物理', 99.0 ), 
 91 ( N'高一4班', N'雷军', N'化学', 97.0 ), 
 92 ( N'高一4班', N'雷军', N'地理', 96.0 ), 
 93 ( N'高一4班', N'雷军', N'生物', 96.0 ), 
 94 ( N'高一4班', N'周鸿祎', N'语文', 91.0 ), 
 95 ( N'高一4班', N'周鸿祎', N'数学', 94.0 ), 
 96 ( N'高一4班', N'周鸿祎', N'英语', 96.0 ), 
 97 ( N'高一4班', N'周鸿祎', N'政治', 99.0 ), 
 98 ( N'高一4班', N'周鸿祎', N'历史', 93.0 ), 
 99 ( N'高一4班', N'周鸿祎', N'物理', 99.0 ), 
100 ( N'高一4班', N'周鸿祎', N'化学', 93.0 ), 
101 ( N'高一4班', N'周鸿祎', N'地理', 100.0 ), 
102 ( N'高一4班', N'周鸿祎', N'生物', 99.0 ), 
103 ( N'高一4班', N'张朝阳', N'语文', 95.0 ), 
104 ( N'高一4班', N'张朝阳', N'数学', 95.0 ), 
105 ( N'高一4班', N'张朝阳', N'英语', 94.0 ), 
106 ( N'高一4班', N'张朝阳', N'政治', 95.0 ), 
107 ( N'高一4班', N'张朝阳', N'历史', 98.0 ), 
108 ( N'高一4班', N'张朝阳', N'物理', 93.0 ), 
109 ( N'高一4班', N'张朝阳', N'化学', 94.0 ), 
110 ( N'高一4班', N'张朝阳', N'地理', 94.0 ), 
111 ( N'高一4班', N'张朝阳', N'生物', 91.0 ), 
112 ( N'高一4班', N'俞敏洪', N'语文', 91.0 ), 
113 ( N'高一4班', N'俞敏洪', N'数学', 95.0 ), 
114 ( N'高一4班', N'俞敏洪', N'英语', 100.0 ), 
115 ( N'高一4班', N'俞敏洪', N'政治', 99.0 ), 
116 ( N'高一4班', N'俞敏洪', N'历史', 91.0 ), 
117 ( N'高一4班', N'俞敏洪', N'物理', 97.0 ), 
118 ( N'高一4班', N'俞敏洪', N'化学', 93.0 ), 
119 ( N'高一4班', N'俞敏洪', N'地理', 93.0 ), 
120 ( N'高一4班', N'俞敏洪', N'生物', 94.0 ), 
121 ( N'高一4班', N'张磊', N'语文', 99.0 ), 
122 ( N'高一4班', N'张磊', N'数学', 99.0 ), 
123 ( N'高一4班', N'张磊', N'英语', 92.0 ), 
124 ( N'高一4班', N'张磊', N'政治', 98.0 ), 
125 ( N'高一4班', N'张磊', N'历史', 97.0 ), 
126 ( N'高一4班', N'张磊', N'物理', 99.0 ), 
127 ( N'高一4班', N'张磊', N'化学', 100.0 ), 
128 ( N'高一4班', N'张磊', N'地理', 91.0 ), 
129 ( N'高一4班', N'张磊', N'生物', 100.0 ), 
130 ( N'高一4班', N'沈南鹏', N'语文', 96.0 ), 
131 ( N'高一4班', N'沈南鹏', N'数学', 92.0 ), 
132 ( N'高一4班', N'沈南鹏', N'英语', 93.0 ), 
133 ( N'高一4班', N'沈南鹏', N'政治', 91.0 ), 
134 ( N'高一4班', N'沈南鹏', N'历史', 94.0 ), 
135 ( N'高一4班', N'沈南鹏', N'物理', 100.0 ), 
136 ( N'高一4班', N'沈南鹏', N'化学', 95.0 ), 
137 ( N'高一4班', N'沈南鹏', N'地理', 91.0 ), 
138 ( N'高一4班', N'沈南鹏', N'生物', 97.0 )

View Code

思路是先分析输出结果和排序条件，这两者影响整个SQL的写法（因为在输出结果和排序中出现的字段都需要在整个SQL中保留，影响输出结果的行颗粒度）。然后把条件拆开来，找到分组单位，聚合条件、过滤和排序规则

1、输出结果是前3名学生的3科学科成绩按学生总分，其次学科成绩高低排序。

2、分组单位是学生

3、任意3门学科总分超过XXX分，意味着是连续聚合，需要从高到低排序并聚合

4、排序取班级前3名

PS.输出结果和排序条件，这两者影响整个SQL的写法，因为在输出结果和排序中出现的字段都需要在整个SQL中保留，影响输出结果的行颗粒度。首先说输出结果，要求只输出学生姓名和3科总成绩（那就是3行数据，每个学生一行），这种是写法上最简单的，直接分组聚合就可以了，难点在任意3门学科总分处理上。如果是要求学生姓名和分别3科学科成绩（即上面的例子），那就是每个学生+3门学科都要展示出来。还有要求你把全部学科成绩都展示出来，但是条件时任意3门学科总分要满足条件。甚至还有更加变态的，要你把每个学生的3门学科以及其他学科成绩汇总成一行（即，每个学生4行数据）。不同的结果展示都直接影响过程的SQL代码写法。原则肯定是最终数据越少越好，数据越多且还需要一些聚合处理的，代码越复杂。当然有些细节点是相似的，例如说任意3科成绩前3，这个肯定无论如何都是要通过排序然后聚合汇总的。先对每个学生的学科成绩进行从高到低的排序，然后取前3高分的学科进行汇总聚合，剔除不满足总分要求的学生。

再说排序条件对SQL写法的影响，举个例子：最终结果只要求输出3科总分成绩，因为这个是硬过滤条件，但是学生排序是所有学科的平均分\总分\甚至特定学科的分数排序。不要以为不可能，以前工作中做过一些的财务报表，报表的排序就是要求用聚合表外的信息字段来排序，有些排序规则还是极其复杂，需要用各种CASE WHEN表达式堆的。

案例1：每个班级任意3门学科总分成绩超过290分的学生，取前3名，相同总分成绩按照3门学科总分里面所含语数英三科成绩总分高低排序。

思路其实很简单，要搞清楚，“任意3门学科总分成绩超过290分的学生前3名”，总分成绩意味着汇总SUM，任意3门学科总成绩超过XX意味着汇总，超过XX肯定是高到低加总更加符合。这里出现了两个标量指，任意3门学科和前3名既是筛选条件也是排序。我一开始以为不用关联查询，直接通过开窗函数DENSE_RANK就能解决，仔细一想是我错了。DENSE_RANK解决的是同值允许并列的问题，最终返回的行数是不确定的（因为我们不知道有多少个值重复），而这个题是要求只能有3个学生，即前三。所以思路转变成先找到哪三个人，再将这三个人作为过滤条件返回他们的前三科目成绩。先通过两个ROW_NUMBER分别返回总分前三且超过290分的学科和排名前三的学生，再关联返回每个学生的学科成绩。

DECLARE @p_total DECIMAL(12,1) = 290

SELECT
 S.[class]
,S.[student]
,S.[subject_name]
,S.[score]
,[TOTAL_SCORE]
,ROW_NO
FROM (
    SELECT [class]
        ,[student]
        ,[TOTAL_SCORE]
        ,[TOTAL_SCORE_YSY]
        ,ROW_NUMBER() OVER (PARTITION BY [class] ORDER BY [TOTAL_SCORE] DESC,[TOTAL_SCORE_YSY] DESC) AS ROW_NO
    FROM (
        SELECT
         [class]
        ,[student]
        ,SUM(CASE WHEN [subject_name] IN (N'语文',N'数学',N'英语') THEN [score] END) AS [TOTAL_SCORE_YSY]
        ,SUM([score]) AS [TOTAL_SCORE]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
        ) AS B
        WHERE B.STU_RN <= 3
        GROUP BY [class]
        ,[student]
) AS T) AS TOP_3
INNER JOIN (
    SELECT
         [class]
        ,[student]
        ,[subject_name]
        ,[score]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
    ) AS B
    WHERE B.STU_RN <= 3
) AS S ON S.[student] = TOP_3.[student] 
AND S.[class] = TOP_3.[class]
AND TOP_3.ROW_NO <= 3
WHERE [TOTAL_SCORE] >= @p_total
ORDER BY [class],ROW_NO,TOP_3.TOTAL_SCORE

结果就是王兴第1，张磊第2，马云第3

案例2：每个班级任意3门学科总分成绩超过290分的学生，取前3名。和案例1不一样的地方，此处允许“并列”。相同总分排名同名次，但是人数还是只能有3个。怎么解释呢？以上面为例，王兴和张磊同分同分，都是298，并列第1名，第3名则和案例1的排序规则一样，按照3门学科总分里面所含语数英三科成绩总分高低排序。思路其实就是利用DENSE_RANK可以实现并列排名的效果，再配合ROW_NUMBER准确限制返回前三名。所以这里DENSE_RANK是起到排名的作用，ROW_NUMBER是筛选的作用。

DECLARE @p_total DECIMAL(12,1) = 290

SELECT
 S.[class]
,S.[student]
,S.[subject_name]
,S.[score]
,[TOTAL_SCORE]
,[DR_NO]
--,[ROW_NO]
FROM (
    SELECT [class]
        ,[student]
        ,[TOTAL_SCORE]
        ,[TOTAL_SCORE_YSY]
        ,[DR_NO]
        ,ROW_NUMBER() OVER (PARTITION BY [class] ORDER BY [TOTAL_SCORE] DESC,[TOTAL_SCORE_YSY] DESC) AS ROW_NO
    FROM (
    SELECT [class]
        ,[student]
        ,[TOTAL_SCORE]
        ,[TOTAL_SCORE_YSY]
        ,DENSE_RANK() OVER (PARTITION BY [class] ORDER BY [TOTAL_SCORE] DESC) AS DR_NO
    FROM (
        SELECT
         [class]
        ,[student]
        ,SUM(CASE WHEN [subject_name] IN (N'语文',N'数学',N'英语') THEN [score] END) AS [TOTAL_SCORE_YSY]
        ,SUM([score]) AS [TOTAL_SCORE]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
        ) AS B
        WHERE B.STU_RN <= 3
        GROUP BY [class]
        ,[student]
) AS T) AS T) AS TOP_3
INNER JOIN (
    SELECT
         [class]
        ,[student]
        ,[subject_name]
        ,[score]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
    ) AS B
    WHERE B.STU_RN <= 3
) AS S ON S.[student] = TOP_3.[student] 
AND S.[class] = TOP_3.[class]
AND TOP_3.ROW_NO <= 3
WHERE [TOTAL_SCORE] >= @p_total
ORDER BY [class],ROW_NO,TOP_3.TOTAL_SCORE

结果

再换一下，如果重复就排名+1，允许排序断开。上面例子为例，王兴和张磊并列第1名，马云第3名。很简单，把上面的DENSE_RANK替换成RANK就可以了。

DECLARE @p_total DECIMAL(12,1) = 290

SELECT
 S.[class]
,S.[student]
,S.[subject_name]
,S.[score]
,[TOTAL_SCORE]
,[DR_NO]
--,[ROW_NO]
FROM (
    SELECT [class]
        ,[student]
        ,[TOTAL_SCORE]
        ,[TOTAL_SCORE_YSY]
        ,[DR_NO]
        ,ROW_NUMBER() OVER (PARTITION BY [class] ORDER BY [TOTAL_SCORE] DESC,[TOTAL_SCORE_YSY] DESC) AS ROW_NO
    FROM (
    SELECT [class]
        ,[student]
        ,[TOTAL_SCORE]
        ,[TOTAL_SCORE_YSY]
        ,RANK() OVER (PARTITION BY [class] ORDER BY [TOTAL_SCORE] DESC) AS DR_NO
    FROM (
        SELECT
         [class]
        ,[student]
        ,SUM(CASE WHEN [subject_name] IN (N'语文',N'数学',N'英语') THEN [score] END) AS [TOTAL_SCORE_YSY]
        ,SUM([score]) AS [TOTAL_SCORE]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
        ) AS B
        WHERE B.STU_RN <= 3
        GROUP BY [class]
        ,[student]
) AS T) AS T) AS TOP_3
INNER JOIN (
    SELECT
         [class]
        ,[student]
        ,[subject_name]
        ,[score]
        FROM (
            SELECT [class]
                  ,[student]
                  ,[subject_name]
                  ,[score]
                  ,ROW_NUMBER() OVER(PARTITION BY [class],[student] ORDER BY [score] DESC) AS STU_RN
            FROM [dbo].[score]
    ) AS B
    WHERE B.STU_RN <= 3
) AS S ON S.[student] = TOP_3.[student] 
AND S.[class] = TOP_3.[class]
AND TOP_3.ROW_NO <= 3
WHERE [TOTAL_SCORE] >= @p_total
ORDER BY [class],ROW_NO,TOP_3.TOTAL_SCORE

结果

案例3：每个班级任意3门学科总分成绩等于290分的学生，取前3名，相同总分成绩按照3门学科总分里面所含语数英三科成绩总分高低排序

posted @ 2022-02-16 18:43 Jerry_Chen 阅读(555) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

T-SQL ->> 找出每个班级里面任意3门学科总分学科超过290分的学生前3名，按总分排序

公告