mysql - 如何在 GCP 中使用 mySQL 获取行号并选择特定行？

Question

我正在将 Google Cloud Platform 与 mySQL 结合使用。

我有一个包含 3 列的表格:

idUser, Name, timestamp
me@you.com, Mike Jones, 1-6-1900 05:00
us@we.com, Steve Brown, 1-2-1900 06:00
jsmith@email.com, John Smith, 1-5-1900 06:00
test@me.com, Sarah Lee, 1-3-1900 05:30

我想进行一个查询，我可以根据时间戳作为顺序/排序，说“查找 jsmith@email.com 所在的行号”。

我已经能够使其部分工作，我可以生成一些结果，显示基于时间戳的行号:

SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,idUser FROM SpeakerQueueActive
ORDER BY timestamp 

这会得到添加行号的结果:

row_num, idUser, Name, timestamp
1,us@we.com, Steve Brown, 1-2-1900 06:00
2,test@me.com, Sarah Lee, 1-3-1900 05:30
3,jsmith@email.com, John Smith, 1-5-1900 06:00
4,me@you.com, Mike Jones, 1-6-1900 05:00

我想我现在想要将其嵌套并使用类似的东西:

SELECT * from
(SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,idUser FROM SpeakerQueueActive
ORDER BY timestamp)
WHERE idUser = 'jsmith@email.com'

但是我收到语法错误的错误。我还尝试将第一个代码块设为存储过程，然后调用它，但我似乎也无法将其与 SELECT 一起使用。

回到我原来的问题，我希望它向我报告他位于第 3 行

我该怎么做？谢谢!

Answer 1

SELECT * from
(SET @row_number:=0;
SELECT @row_number:=@row_number+1 AS row_number,idUser FROM SpeakerQueueActive
ORDER BY timestamp)
WHERE idUser = 'jsmith@email.com'

需要重写

SELECT * from (
   SELECT @row_number:=@row_number+1 AS row_number,idUser FROM SpeakerQueueActive
   CROSS JOIN (SELECT @row_number:= 0) AS init
ORDER BY timestamp) AS alias 
WHERE idUser = 'jsmith@email.com'

或者作为基于逗号的 CROSS JOIN。

SELECT * from (
      SELECT @row_number:=@row_number+1 AS row_number,idUser FROM SpeakerQueueActive
    , (SELECT @row_number:= 0) AS init
ORDER BY timestamp) AS alias 
WHERE idUser = 'jsmith@email.com'