mysql - 将变量传递给嵌套查询

Question

假设user表有200条记录。

下面的查询适用于某个worker_id例如worker_id 2738:

SELECT SUM(something) AS T_BOIN
FROM (
SELECT SUM(A.boin) AS something 
FROM hall A JOIN user B ON A.who=B.id 
WHERE B.worker_id = 2738
ORDER BY total_per_user DESC LIMIT 10
) AS mastertbl

我想像这样对 200 个单独的 worker_id 执行上述查询:

SELECT @TT:=BK.worker_id AS TID, 
 (SELECT SUM(something) AS T_BOIN
 FROM (
  SELECT SUM(A.boin) AS something
  FROM hall A
  JOIN user B ON A.who=B.id
  WHERE B.worker_id = @TT
  ORDER BY total_per_user DESC 
  LIMIT 10) AS mastertbl
 ) AS TEAM
FROM user BK
INNER JOIN (SELECT @TT :=0) AS WESS
GROUP BY TID

但是@tt 超出了范围。

提前致谢。

Answer 1

我认为这可能对你有用......

SELECT worker_id, SUM(something) AS T_BOIN
FROM (
  SELECT b.worker_id, SUM(A.boin) AS something, if(@wid = b.worker_id, @cnt:=@cnt+1, @cnt:=1 AND @wid := b.worker_id) cnt
  FROM hall A
  JOIN user B ON A.who=B.id 
  ORDER BY b.worker_id, total_per_user DESC
) AS mastertbl
WHERE mastertbl.cnt <= 10
GROUP BY worker_id;