mysql - 按特定项目 ID 的行号 ID 过滤

Question

来自 MySQL - Get row number on select我知道如何使用此 mysql 查询获取行号/排名:

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 个简单的 SINGLE QUERY 中获得结果，该查询返回排名低于 itemID 145 的项目，即:

+--------+------+
| rank | itemID |
+--------+------+ 
|  5   |   123  |
|  6   |   345  |
|  7   |   111  |
+--------+------+

也欢迎Oracle sql查询。谢谢。

Answer 1

一个 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 作品有效。