c mysql按照行号删除,mysql-按特定项目ID的行号ID过滤

SELECT @rn:=@rn+1 AS rank, itemID

FROM (

SELECT itemID

FROM orders

ORDER BY somecriteria DESC

) t1, (SELECT @rn:=0) t2;

结果返回如下内容：

+--------+------+

| rank | itemID |

+--------+------+

| 1 | 265 |

| 2 | 135 |

| 3 | 36 |

| 4 | 145 |

| 5 | 123 |

| 6 | 342 |

| 7 | 111 |

+--------+------+

我的问题是：如何在1个简单的单查询中获得结果,该查询返回的等级低于itemID为145的项目,即：

+--------+------+

| rank | itemID |

+--------+------+

| 5 | 123 |

| 6 | 345 |

| 7 | 111 |

+--------+------+

还欢迎使用Oracle sql查询.谢谢.

解决方法:

Oracle解决方案(不确定是否满足“一个简单的单个查询”的条件)：

WITH t AS

(SELECT item_id, row_number() OVER (ORDER BY some_criteria DESC) rn

FROM orders)

SELECT t2.rn, t2.item_id

FROM t t1 JOIN t t2 ON (t2.rn > t1.rn)

WHERE t1.item_id = 145;

我的假设是没有重复的item_id值.

尝试用MySQL术语来表示,也许类似的事情可能起作用：

SELECT t2.rank, t2.itemID

FROM (SELECT @rn:=@rn+1 AS rank, itemID

FROM (SELECT itemID

FROM orders

ORDER BY somecriteria DESC), (SELECT @rn:=0)) t1 INNER JOIN

(SELECT @rn:=@rn+1 AS rank, itemID

FROM (SELECT itemID

FROM orders

ORDER BY somecriteria DESC), (SELECT @rn:=0)) t2 ON t2.rank > t1.rank

WHERE t1.itemID = 145;

免责声明：我没有太多使用MySQL,并且未经测试. Oracle作品奏效.

标签：oracle,sql,mysql

来源： https://codeday.me/bug/20191127/2076536.html