如何在查询中标记x最大数字？

Question

给出以下简单的表格：

Value
10
4
7
2
4
6

如何编写选择查询，标记为x最大值。例如，如果标记了3个最大值，则在所需的查询输出下面。

Value   IsBiggest
10      true
4       false
7       true
2       false
4       false
6       true

Answer 1

一种可能的方式是：

(select value, 'true' as IsBiggest from simple_table order by value desc limit 3)
union all
(select value, 'false' as IsBiggest from simple_table order by value desc limit 3, 9223372036854775807)

Answer 2

这应该能起作用：

SELECT test.value, 
       CASE WHEN X.isBiggest IS NULL THEN false ELSE true END isBiggest
FROM test 
    LEFT JOIN (
        SELECT value, true isBiggest
        FROM test 
        ORDER BY value DESC
        LIMIT 0,3
    ) X ON
    test.value = X.value

如果你只知道前3名：

SELECT value
FROM test 
ORDER BY value DESC
LIMIT 0,3

Answer 3

你可以试试这个

SELECT tb1.value , IF(tb2.isBiggest ,1, 0) AS isBiggest FROM `sample_table` tb1 LEFT JOIN (SELECT value , 1 AS isBiggest
FROM  `sample_table` 
ORDER BY value DESC 
LIMIT 3
) tb2 ON tb1.value = tb2.value