MySQL 子查询 SUM 限制

Question

我正在尝试使用 SUM() 和 LIMIT 进行子查询。这适用于以下代码:

SELECT id, 
    (
        SELECT SUM(number) 
        FROM (
            SELECT number 
            FROM t2
            WHERE u_id = '1' 
            ORDER BY time ASC 
            LIMIT 30
        ) AS temp
    )  AS test
FROM t1

但我当然想使用当前行 ID 动态地执行此操作。我将查询更改为以下内容:

SELECT id, 
    (
        SELECT SUM(number) 
        FROM (
            SELECT number
            FROM t2 
            WHERE u_id = p.id 
            ORDER BY time ASC 
            LIMIT 30
        ) AS temp
    )  AS test
FROM t1 p

这将给出以下错误:

Unknown column 'p.id' in 'where clause'

有什么想法可以让它发挥作用吗？

Answer 1

不幸的是，MySQL 限制了表别名的范围。 Oracle 是另一个执行此操作的数据库。

您可以将您的查询表述为一个复杂的连接:

select t1.id, sum(t2.number)
from t1 p join
     t2
     on p.id = t2.u_id
where 30 >= (select count(*)
             from t2 t22
             where t22.u_id = t2.u_id and
                   t22.time <= t2.time
            )
group by t1.id;

或者你可以用变量来做到这一点:

select p.id, sum(number)
from t1 p join
     (select t2.*,
             @rn := if(@u = t2.u_id, @rn + 1, if((@u := t2.u_id) is not null, 1, 0)) as rn
      from t2
           (select @u := 0, @rn := 0) vars
      order by t2.u_d, time
     ) t2
     on p.id = t2.u_id
where rn <= 30
group by p.id;