MySQL 替代 T-SQL 的 WITH TIES

2021-11-20 00:00:00 sql mysql

我有一张表,我想从中获取前 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

相关文章