mysql查询错误

Question

select id, s
from (
         select o_user_id as id, sum(total_price) as s
         from Orders o
         group by o.o_user_id
     ) as t1
where s = (select max(t1.s) from t1)

它返回一个错误，表示表 t1 不存在。

我想找出所有订单中消费最多的用户的id

这是订单表

Answer 1

该别名超出了子查询的范围

select id, s
from (
         select o_user_id as id, sum(total_price) as s
         from Orders o
         group by o.o_user_id
     ) as t1
where s = (select max(t1.s) from t1)

你可以做到

WITH T1 AS 
    (
      select o_user_id as id, sum(total_price) as s
         from Orders o
         group by o.o_user_id
     ) 
SELECT id, s
  FROM T1
 WHERE s = (select max(t1.s) from t1);