MySQL 替代 T-SQL 的 WITH TIES
我有一张表，我想从中获取前 N 条记录.记录按值排序，有些记录具有相同的值.我想在这里做的是获取前 N 条记录的列表，包括并列记录.这是表中的内容:
I have a table from which I want to get the top N records. The records are ordered by values and some records have the same values. What I'd like to do here is to get a list of top N records, including the tied ones. This is what's in the table:
+-------+--------+
| Name | Value |
+-------+--------+
| A | 10 |
| B | 30 |
| C | 40 |
| D | 40 |
| E | 20 |
| F | 50 |
+-------+--------+
现在如果我想像这样获得前三名
Now if I want to get the top 3 like so
SELECT * FROM table ORDER BY Value DESC LIMIT 3
我明白了:
+-------+--------+
| Name | Value |
+-------+--------+
| F | 50 |
| C | 40 |
| D | 40 |
+-------+--------+
我想要的是这个
+-------+--------+
| Name | Value |
+-------+--------+
| F | 50 |
| C | 40 |
| D | 40 |
| B | 30 |
+-------+--------+
我计算了每条记录的排名，所以我真正想要的是获得前 N 条排名记录，而不是按值排序的前 N 条记录.这是我计算排名的方式:
I calculate the rank of each record so what I would really like is to get the first N ranked records instead of the first N records ordered by value. This is how I calculate the rank:
SELECT Value AS Val, (SELECT COUNT(DISTINCT(Value))+1 FROM table WHERE Value > Val) as Rank
在 T-SQL 中，这样的事情可以通过这样做来实现:
In T-SQL something like this is achievable by doing this:
SELECT TOP 3 FROM table ORDER BY Value WITH TIES
有谁知道如何在 MySQL 中做到这一点?我知道它可以通过子查询或临时表来完成，但我没有足够的知识来完成这个.我更喜欢不使用临时表的解决方案.
Does anyone have an idea how to do this in MySQL? I understand it could be done with subqueries or temporary tables but I don't have enough knowledge to accomplish this. I'd prefer a solution without using temporary tables.
推荐答案
这对您有用吗?
select Name, Value from table where Value in (
select distinct Value from table order by Value desc limit 3
) order by Value desc
或者:
select a.Name, a.Value
from table a
join (select distinct Value from table order by Value desc limit 3) b
on a.Value = b.Value
相关文章