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