mysql - 尝试在 MySQL SubSubQuery 中使用 ID

Question

所以我将向您展示我正在尝试做的事情并解释我的问题，可能会有与我尝试采用的方法不同的答案。

我尝试执行的查询如下:

SELECT *
FROM report_keywords rk
WHERE rk.report_id = 231
AND (
    SELECT SUM(t.conv) FROM (
        SELECT conv FROM report_keywords t2 WHERE t2.campaign_id = rk.campaign_id ORDER BY conv DESC LIMIT 10
    ) t
) >= 30
GROUP BY rk.campaign_id

我得到的错误是

Unknown column 'rk.campaign_id' in 'where clause'

显然这是在说表别名 rk 没有进入子子查询。我想要做的是获取前 10 位转化总和大于或等于 30 的所有广告系列。

相关表结构为:

id INT,
report_id INT,
campaign_id INT,
conv INT

如有任何帮助，我们将不胜感激。

更新

多亏了 Kickstart，我才能够做我想做的事。这是我的最终查询:

SELECT campaign_id, SUM(conv) as sum_conv
FROM (
    SELECT campaign_id, conv, @Sequence := if(campaign_id = @campaign_id, @Sequence + 1, 1) AS aSequence, @campaign_id := campaign_id
    FROM report_keywords
    CROSS JOIN (SELECT @Sequence :=  0, @campaign_id := 0) Sub1
    WHERE report_id = 231
    ORDER BY campaign_id, conv DESC
) t
WHERE aSequence <= 10
GROUP BY campaign_id
HAVING sum_conv >= 30

Answer 1

可能使用用户变量添加序列号以获取每条记录的最新 10 条记录，然后使用 SUM 获取这些记录的计数。

像这样:-

SELECT rk.*
FROM report_keywords rk
INNER JOIN
(
    SELECT campaign_id, SUM(conv) AS SumConv
    FROM 
    (
        SELECT campaign_id, conv, @Sequence := if(campaign_id = @campaign_id, @Sequence + 1, 1) AS aSequence, @campaign_id := campaign_id
        FROM report_keywords
        CROSS JOIN (SELECT @Sequence :=  0, @campaign_id := "") Sub1
        ORDER BY campaign_id, conv
    ) Sub2
    WHERE aSequence <= 10
    GROUP BY campaign_id
) Sub3
ON rk.campaign_id = Sub3.campaign_id AND Sub3.SumConv >= 30
WHERE rk.report_id = 231