MySQL5.7版本没有row_number函数的写法

MySQL5.7版本没有row_number函数的写法

需求：一个诊断名称对应了多个MASTER_ID(诊断编码),取出出现次数最多的那个MASTER_ID(诊断编码)。

学习网址

#1.按照诊断名称分组,过滤出MASTER_ID(诊断编码)大于3次的记录。
SELECT DIAGNOSIS_NAME, COUNT(DISTINCT MASTER_ID)
FROM ICD_10
GROUP BY DIAGNOSIS_NAME
HAVING COUNT(DISTINCT MASTER_ID) > 3;

#2.查看数据,可以看到1个诊断名称对应了4种MASTER_ID(诊断编码)。
SELECT *
FROM ICD_10
WHERE DIAGNOSIS_NAME = '妊娠合并肠炎'
    AND MASTER_ID REGEXP '[^[:blank:]]';

#3.按照诊断名称和MASTER_ID(诊断编码)分组统计记录数,按照诊断名称和记录数降序排序。
#注意：子查询里面先把MASTER_ID(诊断编码)为空的记录过滤掉了。
SELECT T1.DIAGNOSIS_NAME, MASTER_ID, COUNT(1) NUM
FROM (
    SELECT MASTER_ID, DIAGNOSIS_NAME
    FROM ICD_10
    WHERE MASTER_ID IS NOT NULL
        AND DIAGNOSIS_NAME = '妊娠合并肠炎'
    ) T1
GROUP BY T1.DIAGNOSIS_NAME, MASTER_ID
ORDER BY DIAGNOSIS_NAME, NUM DESC

#4.取RN=1的记录,就是取出诊断编码出现最多的那一个。
CREATE TABLE TZ_DIAG_STAND
SELECT *
FROM (
    SELECT @RN: = CASE WHEN @DIAGNOSIS_NAME = DIAGNOSIS_NAME THEN @RN + 1 ELSE 1 END AS RN, 
    @DIAGNOSIS_NAME: = DIAGNOSIS_NAME AS DIAG_NAME, MASTER_ID, NUM
    FROM (
        SELECT T1.DIAGNOSIS_NAME, MASTER_ID, COUNT(1) NUM
        FROM (
            SELECT MASTER_ID, DIAGNOSIS_NAME
            FROM ICD_10
            WHERE MASTER_ID IS NOT NULL
                AND DIAGNOSIS_NAME = '妊娠合并肠炎'
            ) T1
        GROUP BY T1.DIAGNOSIS_NAME, MASTER_ID
        ORDER BY DIAGNOSIS_NAME, NUM DESC
        ) A, (
            SELECT @RN = 0, @DIAGNOSIS_NAME = 0
            ) B
    ) A
WHERE RN = 1;

#5.MYSQL5.7版本没有ROW_NUMBER函数的写法
CREATE TABLE TZ_DIAG_STAND
SELECT *
FROM (
    SELECT @RN: = CASE WHEN @DIAGNOSIS_NAME = DIAGNOSIS_NAME THEN @RN + 1 ELSE 1 END AS RN, 
    @DIAGNOSIS_NAME: = DIAGNOSIS_NAME AS DIAG_NAME, MASTER_ID, NUM
    FROM (
        SELECT T1.DIAGNOSIS_NAME, MASTER_ID, COUNT(1) NUM
        FROM (
            SELECT MASTER_ID, DIAGNOSIS_NAME
            FROM ICD_10
            WHERE MASTER_ID IS NOT NULL
                AND DIAGNOSIS_NAME = '妊娠合并肠炎'
            ) T1
        GROUP BY T1.DIAGNOSIS_NAME, MASTER_ID
        ORDER BY DIAGNOSIS_NAME, NUM DESC
        ) A, (
            SELECT @RN = 0, @DIAGNOSIS_NAME = 0
            ) B
    ) A
WHERE RN = 1;

#6.取出每一个诊断,对应出现次数最多的那个MASTER_ID(诊断编码)。
CREATE TABLE TZ_DIAG_STAND
SELECT *
FROM (
    SELECT @RN: = CASE WHEN @DIAGNOSIS_NAME = DIAGNOSIS_NAME THEN @RN + 1 ELSE 1 END AS RN, 
    @DIAGNOSIS_NAME: = DIAGNOSIS_NAME AS DIAG_NAME, MASTER_ID, NUM
    FROM (
        SELECT T1.DIAGNOSIS_NAME, MASTER_ID, COUNT(1) NUM
        FROM (
            SELECT MASTER_ID, DIAGNOSIS_NAME
            FROM ICD_10
            WHERE MASTER_ID IS NOT NULL
            ) T1
        GROUP BY T1.DIAGNOSIS_NAME, MASTER_ID
        ORDER BY DIAGNOSIS_NAME, NUM DESC
        ) A, (
            SELECT @RN = 0, @DIAGNOSIS_NAME = 0
            ) B
    ) A
WHERE RN = 1;