php - mysql 选择行号查询

Question

我在尝试根据表的某个子集中的(赞成票 - 反对票)选择行号时遇到此查询问题，但我还需要在同一查询中重置@rownum:

这个查询有效，但我需要运行其中两个。无论如何，将 @rownum 集合合并到一个查询中，我的尝试都是徒劳的。

SET @rownum = 0;
SELECT rank  
FROM (SELECT *, @rownum:=@rownum + 1 AS 
rank FROM Cars where Auto_SET = 'BMW'
order by (upvotes-downvotes) DESC) d
WHERE ID = 391802

这会引发错误:

SELECT rank 
FROM (SELECT *, @rownum:=@rownum + 1 AS 
rank FROM Cars  where Auto_SET = 'BMW' , 
(SELECT @rownum:=0) r order by 
(upvotes-downvotes) DESC) d
WHERE ID = 391913

Answer 1

交叉连接应该与select from table_name类似

SELECT rank 
FROM (
  SELECT *, 
  @rownum:=@rownum + 1 AS rank 
  FROM Cars ,(SELECT @rownum:=0) r 
  where Auto_SET = 'BMW' 
  order by 
 (upvotes-downvotes) DESC
) d
WHERE ID = 391913