mysql with t,MySQL替代T-SQL的WITH TIES

快来打我* 2022-10-04 12:55 45阅读 0赞

![Image 1][]

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

I get this:

\+-------+--------+

| Name | Value |

\+-------+--------+

| F | 50 |

| C | 40 |

| D | 40 |

\+-------+--------+

What I would like to get is this

\+-------+--------+

| Name | Value |

\+-------+--------+

| F | 50 |

| C | 40 |

| D | 40 |

| B | 30 |

\+-------+--------+

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

In T-SQL something like this is achievable by doing this:

SELECT TOP 3 FROM table ORDER BY Value WITH TIES

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.

解决方案

Does this work for you?

select Name, Value from table where Value in (

select distinct Value from table order by Value desc limit 3

) order by Value desc

Or perhaps:

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

[Image 1]:

发表评论取消回复

表情：

评论列表（有 0 条评论，45人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL 中 WITH 用法详解

一、什么是 WITH 子句 1. 定义 WITH 子句是 MySQL 中的一种 SQL 结构，又称为 Common Table Expression (CTE)。它在不

刺骨的言语ヽ痛彻心扉/ 2023年10月13日 21:42/ 0 赞/ 63 阅读

相关 ansible playbook中的循环（三）-----（with_cartesian，with_nested和with_indexed_items）

前文中我们已经了解了一些用于循环的关键字，比如 with\_list、with\_items、with\_flattened、with\_together，这篇文章我们继续。

梦里梦外;/ 2023年07月22日 03:55/ 0 赞/ 1 阅读

相关 Mysql中的WITH ROLLUP用法

[Mysql中的WITH ROLLUP用法][Mysql_WITH ROLLUP] ![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.pn

悠悠/ 2022年11月20日 05:52/ 0 赞/ 269 阅读

相关 MySQL with语句小结

备注:测试数据库版本为MySQL 8.0 这个blog我们来聊聊MySQL的with语句对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护

缺乏、安全感/ 2022年11月19日 04:23/ 0 赞/ 238 阅读

相关 mysql with t,MySQL替代T-SQL的WITH TIES

![Image 1][] I have a table from which I want to get the top N records. The records are

快来打我*/ 2022年10月04日 12:55/ 0 赞/ 46 阅读

相关 MySQL GROUP BY WITH ROLLUP

类似Oracle中的rollup，不同的是不能排序，select后面查询的字段可以不是group by的字段。 SELECT a.actor\_id,COUNT(1) cc

￡神魔★判官ぃ/ 2022年05月31日 01:08/ 0 赞/ 220 阅读

相关 mySQL没有 with as

注：此文就是发发牢骚！之前一直用oracle ,SQLServer等，用with as 作为虚拟表去处理数据很上手，忽然，用mySQL去处理数据时，才发现mySQL不支持w

梦里梦外;/ 2022年05月21日 07:28/ 0 赞/ 171 阅读

相关 with语句

一语法 with语句被用于在访问一个对象的属性或方法时避免重复使用指定对象引用。语法： with(object) \{ stataments \} objec

电玩女神/ 2022年05月11日 23:22/ 0 赞/ 202 阅读

相关 Hibernate Batch Processing with MySQL

Batch processing is a common solution to improve database performance by inserting or up

ゝ一世哀愁。/ 2021年12月07日 09:51/ 0 赞/ 292 阅读

相关 python with

with 的作用很像 try…except…finally file = open("/tmp/foo.txt") try: data =

分手后的思念是犯贱/ 2021年06月24日 15:56/ 0 赞/ 380 阅读