mysql select查询直到达到条件

Question

假设我的用户表中有用户列表，如下所示。我需要从我的表中找到用户数，直到 userid 等于 100。

所以这里的答案是 (3)。但是我怎么能找到这是 MySQL 查询。有什么想法吗？

userid    name
---------------
10        aaa
30        bbb
100       ccc
60        ddd

Answer 1

这是实现您的期望的解决方法之一。

SET @row_number:=0;

SELECT A.row_number FROM (
    SELECT Userid, name, @row_number:=@row_number+1 AS row_number  
    FROM UserDetail
) AS A
WHERE A.Userid = 100;

Working DEMO

如果 UserId 不是唯一 ID 并且它可以重复，您可以添加 ORDER BY 和 LIMIT 1

SET @row_number:=0;

SELECT A.row_number FROM (
    SELECT Userid, name, @row_number:=@row_number+1 AS row_number  
    FROM UserDetail
) AS A
WHERE A.Userid = 100
ORDER BY A.row_number
LIMIT 1;

Working DEMO

给定数据的示例执行:

--DROP TABLE UserDetail;

CREATE TABLE UserDetail (Userid INT,  name VARCHAR (50));

INSERT INTO UserDetail (Userid, name) VALUES
(10 , 'Aaa'),
(30 , 'Bbb'),
(100, 'Ccc'),
(60 , 'ddd');

SET @row_number:=0;

SELECT A.row_number FROM (
    SELECT Userid, name, @row_number:=@row_number+1 AS row_number  
    FROM UserDetail
) AS A
WHERE Userid = 100;