求分组最大值

[zlw0905]

现在想求每个人每张试卷的最高成绩，Ksj_Ren这个表是所有参加考试的人，Kaoshi_Master是将成绩记录在里边的，可以允许多次考试，所以取最高成绩作为这个人的成绩，用的LeftJion是也需要查询出没参加考试的人
我写了个sql语句是这样的

SELECT  kr.SjID AS '试卷ID' ,
        kr.Dlm AS '登录名' ,
        MAX(km.Kscj) AS '最大成绩'
FROM    exam1.dbo.Ksj_Ren kr --考试人员表
        LEFT JOIN exam1.dbo.Kaoshi_Master AS km ON km.Dlm = kr.Dlm --考试记录表
WHERE   km.Dlm = '00697'
GROUP BY kr.SjID ,
        kr.Dlm

这个结果查出来了工号为00697的每个试卷的最高成绩，很明显结果是错误的，查出来的是这个人所有考试的最高成绩，分组并没有起作用

[zlw0905]

搞出来了

WITH    t1
          AS ( -- 最基本的分组依据
               SELECT DISTINCT
                        Dlm ,
                        SjID
               FROM     exam1.dbo.Kaoshi_Master
             ),
        t2
          AS ( SELECT   x.*
               FROM     t1
                        CROSS APPLY ( -- 每个分组取最大一条
                                      SELECT TOP 1
                                                *
                                      FROM      exam1.dbo.Kaoshi_Master a
                                      WHERE     a.Dlm = t1.Dlm
                                                AND a.SjID = t1.SjID
                                      ORDER BY  Kscj DESC
                                    ) x
             )
    SELECT  kr.Dlm '登录账号' ,
            km.SjID '试卷ID' ,
            km.Kscj '最高成绩'
    FROM    exam1.dbo.Ksj_Ren kr
            LEFT JOIN t2 km ON kr.Dlm = km.Dlm
                               AND km.SjID = kr.SjID
    WHERE   kr.Dlm = '00697'
    ORDER BY km.Dlm

[卖水果的net]

原始数据是什么样子的，还有表结构；

[中国风]

;WITH CTE
AS
(
SELECT  kr.SjID AS '试卷ID' ,
        kr.Dlm AS '登录名' ,
        km.Kscj AS '最大成绩',
		RANK()OVER(PARTITION BY kr.Dlm ORDER BY km.Kscj DESC) AS RN
FROM    exam1.dbo.Ksj_Ren kr --考试人员表
        LEFT JOIN exam1.dbo.Kaoshi_Master AS km ON km.Dlm = kr.Dlm --考试记录表
WHERE   km.Dlm = '00697'
)
SELECT * FROM CTE WHERE RN=1

[zlw0905]

引用 1 楼 wmxcn2000 的回复:

原始数据是什么样子的，还有表结构；

这是原始数据，试卷对应着成绩，如果考试多次会有多条记录，比如sjid=1737的，就有5条，最高成绩应该是64

[zlw0905]

引用 2 楼 roy_88 的回复:

;WITH CTE
AS
(
SELECT  kr.SjID AS '试卷ID' ,
        kr.Dlm AS '登录名' ,
        km.Kscj AS '最大成绩',
		RANK()OVER(PARTITION BY kr.Dlm ORDER BY km.Kscj DESC) AS RN
FROM    exam1.dbo.Ksj_Ren kr --考试人员表
        LEFT JOIN exam1.dbo.Kaoshi_Master AS km ON km.Dlm = kr.Dlm --考试记录表
WHERE   km.Dlm = '00697'
)
SELECT * FROM CTE WHERE RN=1

感谢回复，但好像还是没用啊

[中国风]

有多份试券相同分，要显示试券ID，重复是必须的

[中国风]

首先要检查 条件是否正确 km.Dlm = kr.Dlm 没问题时同个登陆名多份试券相同成绩时怎么显示？ 看你贴出来的结果同语句执行结果明显是不同的数据

[zlw0905]

虽然没有满意回答，还是把分给最热心的人吧