Salman A Jan Hančič

^{这个 original query 使用的用户变量和 ORDER BY 在派生表上;不能保证这两个怪癖的行为。修改后的答案如下。}

在mysql 5.x中,可以使用poor man的rank over partition来获得所需的结果。只需将表与自身进行外部连接,对于每一行,计算行数 较小的 比它。在上述情况下,较小的行是具有较高速率的行:

SELECT t.id, t.rate, t.year, COUNT(l.rate) AS rank
FROM t
LEFT JOIN t AS l ON t.id = l.id AND t.rate < l.rate
GROUP BY t.id, t.rate, t.year
HAVING COUNT(l.rate) < 5
ORDER BY t.id, t.rate DESC, t.year

Demo and Result :

| id  | rate | year | rank |
|-----|------|------|------|
| p01 |  8.0 | 2006 | 0    |
| p01 |  7.4 | 2003 | 1    |
| p01 |  6.8 | 2008 | 2    |
| p01 |  5.9 | 2001 | 3    |
| p01 |  5.3 | 2007 | 4    |
| p02 | 12.5 | 2001 | 0    |
| p02 | 12.4 | 2004 | 1    |
| p02 | 12.2 | 2002 | 2    |
| p02 | 10.3 | 2003 | 3    |
| p02 |  8.7 | 2000 | 4    |

请注意,如果费率有关联,例如:

100, 90, 90, 80, 80, 80, 70, 60, 50, 40, ...

上面的查询将返回6行:

100, 90, 90, 80, 80, 80

更改为 HAVING COUNT(DISTINCT l.rate) < 5 要获得8行:

100, 90, 90, 80, 80, 80, 70, 60

或改为 ON t.id = l.id AND (t.rate < l.rate OR (t.rate = l.rate AND t.pri_key > l.pri_key)) 要获得5行:

 100, 90, 90, 80, 80

在mysql 8或更高版本中,只需使用 RANK , DENSE_RANK or ROW_NUMBER 功能:

SELECT *
FROM (
    SELECT *, RANK() OVER (PARTITION BY id ORDER BY rate DESC) AS rnk
    FROM t
) AS x
WHERE rnk <= 5

你可以用 NOT EXISTS 检查2018年是否没有记录:

SELECT dateFin, codeAdherent, codeArticle
FROM hs AS t
WHERE codeFamilleArticle IN ('CNI', 'COT', 'ABO', 'ABOW', 'CNIW', 'O&T', 'EPH', 'TAX')
AND codeAdherent != 0
-- filter 2017 rows
AND dateFin >= '2017-01-01'
AND dateFin <  '2018-01-01'
-- filter rows where 2018 data does not exist
AND NOT EXISTS (
    SELECT 1
    FROM hs
    WHERE codeAdherent = t.codeAdherent
    AND dateFin >= '2018-01-01'
)