Oracle SQL通过Kocury表自连接实现吃鼠总数排名前N行查询

需求说明 #

查找按总吃鼠数量排名前n位的猫，总吃鼠量相同的猫排名相同，要求通过Kocury表自连接实现。

现有问题 #

原有错误实现在n=1时返回了Tygrys和Lysy两只猫，正确结果应仅返回总吃鼠量最高的Tygrys。

已验证正确的非连接实现方案 #

-- 方案1：子查询统计更高吃鼠量的distinct数量实现
SELECT pseudo, przydzial_myszy + NVL(myszy_extra, 0) "ZJADA"
FROM Kocury K
WHERE (SELECT COUNT(DISTINCT przydzial_myszy + NVL(myszy_extra, 0)) FROM Kocury
      WHERE przydzial_myszy + NVL(myszy_extra, 0) > K.przydzial_myszy + NVL(K.myszy_extra, 0)) < 6
ORDER BY 2 DESC;

-- 方案2：子查询取前N位吃鼠量后IN匹配实现
SELECT pseudo, przydzial_myszy + NVL(myszy_extra, 0) "ZJADA"
FROM Kocury
WHERE przydzial_myszy + NVL(myszy_extra, 0) IN (
  SELECT *
  FROM (
    SELECT DISTINCT przydzial_myszy + NVL(myszy_extra, 0)
    FROM Kocury
    ORDER BY 1 DESC
  ) WHERE ROWNUM <= 6
);

-- 方案3：DENSE_RANK窗口函数实现
SELECT pseudo, ZJADA
FROM
(
  SELECT pseudo,
    NVL(przydzial_myszy, 0) + NVL(myszy_extra, 0) "ZJADA",
    DENSE_RANK() OVER (
      ORDER BY przydzial_myszy + NVL(myszy_extra, 0) DESC
    ) RANK
  FROM Kocury
)
WHERE RANK <= 6;

符合要求的自连接实现方案（方案4） #

本方案使用LEFT JOIN自连接实现，逻辑等价于DENSE_RANK排名规则，总吃鼠量相同的猫排名一致，n=1时仅返回最高吃鼠量的猫，修改HAVING条件中的数值即可调整取前N位：

SELECT k1.pseudo, k1.ZJADA
FROM (
    -- 先计算每只猫的总吃鼠量
    SELECT pseudo, przydzial_myszy + NVL(myszy_extra, 0) AS ZJADA
    FROM Kocury
) k1
-- 左连接所有不同的总吃鼠量值，匹配比当前猫吃鼠量更高的记录
LEFT JOIN (
    SELECT DISTINCT przydzial_myszy + NVL(myszy_extra, 0) AS ZJADA
    FROM Kocury
) k2 ON k2.ZJADA > k1.ZJADA
GROUP BY k1.pseudo, k1.ZJADA
-- 统计比当前猫更高的不同吃鼠量数量，小于N即代表排名在前N位
HAVING COUNT(k2.ZJADA) < 6
ORDER BY k1.ZJADA DESC;

内容的提问来源于stack exchange，提问作者Jakub Kowal