MySQL替代T-SQL的WITH TIES [英] MySQL's alternative to T-SQL's 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
这篇关于MySQL替代T-SQL的WITH TIES的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
