php - mysql 内连接和动态排名计算错误#1241

Question

我正在尝试计算用户在我的门户中的排名...我将用户名从 user_table_name 连接到 rank_table 并动态计算排名，如果我单独运行查询，它运行良好，但是当我一起使用它时，出现 #1241 错误这是我的代码

SELECT (SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
               rt.stock_amount, rt.fd_amount, rt.user_money
        FROM `user_table_name` AS utn INNER JOIN
             `rank_table` AS rt on rt.user_id=utn.userid
       ) AS ut, 
       (SELECT  COUNT(*)
        FROM    rank_table ui
        WHERE   (ui.amount_left) >= (uo.amount_left)
       ) AS rank 
FROM    rank_table uo
WHERE   1
ORDER BY rank ASC  

感谢戈登的以下查询

SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
   rt.stock_amount, rt.fd_amount, rt.user_money,
   (SELECT  COUNT(*)
     FROM    rank_table ui
     WHERE   (ui.amount_left) >= (rt.amount_left)
   ) AS rank
FROM `user_table_name` utn INNER JOIN
 `rank_table` rt
  on rt.user_id = utn.userid
ORDER BY rank ASC;

但是查询给了我奇怪的结果，例如如果 10 和 11 位置的用户有相同的点，那么排名显示为 11 而不是 10..所以你能帮忙

Answer 1

select 的子查询只能返回一列。不是一群。我想你想要这样的东西:

SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
       rt.stock_amount, rt.fd_amount, rt.user_money,
       (SELECT  COUNT(*)
         FROM    rank_table ui
         WHERE   (ui.amount_left) >= (rt.amount_left)
       ) AS rank
FROM `user_table_name` utn INNER JOIN
     `rank_table` rt
      on rt.user_id = utn.userid
ORDER BY rank ASC;