如何在 MySQL 中選擇前 N 個最大值：僅傳回 N 行或包含並列？

MySQL: Selecting the Top N Maximum Values

Confusion often arises when attempting to retrieve the top N rows with the highest values in a specific column. The question is whether to return only the top N rows or include additional rows if they share the same maximum value.

To achieve the former, the following query will suffice:

SELECT *
FROM t
ORDER BY value DESC
LIMIT N

This query ensures that only the top N rows are returned, regardless of whether other rows have the same maximum value.

However, if the requirement is to include rows with equal maximum values, a more complex query is required:

SELECT *
FROM t
JOIN (
  SELECT MIN(value) AS cutoff
  FROM (
    SELECT value
    FROM t
    ORDER BY value
    LIMIT N
  ) tlim
) tlim
ON t.value >= tlim.cutoff;

Alternatively, the query can be simplified:

SELECT *
FROM t
JOIN (
  SELECT value
  FROM t
  ORDER BY value
  LIMIT N
) tlim
ON t.value = tlim.value;

Lastly, another method involves using the ANY function, but its compatibility with MySQL may vary:

SELECT *
FROM t
WHERE t.value >= ANY (
  SELECT value
  FROM t
  ORDER BY value
  LIMIT N
)

This approach also includes rows with the same maximum value, offering a conceptually clear solution to the problem.

以上是如何在 MySQL 中選擇前 N 個最大值：僅傳回 N 行或包含並列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！