Oracle Row_Number()函数用法
需求:一个诊断名称对应多个诊断编码,取出现次数最多的那一个。
#succeed
WITH tmp
AS (
SELECT t1."diagnosis_name", t1."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", t1."master_id"
ORDER BY "diagnosis_name", num DESC
)
SELECT "diagnosis_name", "master_id", num,rn
FROM (
SELECT "diagnosis_name", "master_id", num, row_number() OVER (
PARTITION BY "diagnosis_name" ORDER BY "diagnosis_name", num DESC
) AS RN
FROM tmp
) T
WHERE T.RN = 1;
注意:字段如果不用双引号引起来,执行会报错:例如:> ORA-00904: “MASTER_ID”: 标识符无效。
原因:Oracle在创建表的时候,字段名称(小写)使用双引号引起来了,所以在后续查询的时候也必须是小写,而且还要用引号引起来。(Oracle中默认把字段从小写变成大写,但是加了双引号的话,字段中小写就是小写,不会变成大写)
解决办法1:重新在建张表,把双引号去掉就好了。(建议使用这种方式)
解决办法2:重新在建张表,将双引号中的字段名称改成大写。