mysql - 使用 CASE 需要来自另一条记录的数据

Question

我在 SELECT 语句中使用了 CASE 语句，按理说应该将其分解为更小的部分，但我距离完成这项任务还差一步，并且已经到了不归路。

在不将整个查询放在这里的情况下，问题的要点是:

SELECT
    Dogs,
    Cats,
    COUNT(DISTINCT CASE WHEN my.my_id = 2765 THEN my.data END) AS CountAccounts,
    COUNT(DISTINCT CASE WHEN my.my_id = 3347 THEN my.data END) AS CountUpgradedAccounts

my 指的是一个表，它是名称值对的查找表。当 my.my_id = 3347 时，表示帐户“升级”，数据点是帐户升级的日期。当my.my_id = 2765时，表示创建账户，对应的数据点就是accountID。

我的它看起来像这样:

UserID | my_id | Data
374    | 2765  | 8826
487    | 3347  | 2013-09-01
662    | 2765  | 8826
321    | 2765  | 9213
722    | 3347  | 2014-10-14
852    | 2765  | 8826
487    | 2765  | 9213

当 my_id = 2765 时，我想要与其相关的 accountID 的不同数量。在上表中，即 2:帐户 8826 和 9213。

我知道如果我只从我的中提取数据，这会非常简单。但是 my 以一种使事情变得复杂的方式融入了我的查询中。

其实这里就是查询，或许问题会更容易看出。请注意，问题在于 SELECT 语句中选择的最后一个字段。我不想计算不同的日期，我想计算已升级的不同的 accountID。:

SELECT 
    sub.name AS ARName,
    sub.desc AS ARDescription,
    m.name AS MessageName,
    m.subj AS MessageDescription,
    clk.type AS EventType,
    COUNT(DISTINCT clk.eid) AS CountAdmins,
    COUNT(DISTINCT CASE WHEN my.my_id = 3347 THEN clk.eid END) AS CountUpgradeAdmins,
    COUNT(DISTINCT CASE WHEN my.my_id = 2765 THEN my.data END) AS CountAccounts,
    COUNT(DISTINCT CASE WHEN my.my_id = 3347 THEN my.data END) AS CountUpgradedAccounts # <-- THIS LINE IS THE PROBLEM
FROM
    bata.sseq seq 
INNER JOIN bata.messages m 
    ON m.id = seq.mid 
INNER JOIN bm_arc.clicks208 clk 
    ON clk.camp = seq.camp 
INNER JOIN bemails.cid cid 
    ON cid.id = clk.eid 
INNER JOIN bonfig.sub
    ON sub.id = seq.sid
LEFT JOIN bemails.my208 my 
    ON cid.id = my.eid AND (my_id = 3347 OR my_id = 2765) # only return people who upgraded and accountIDs
WHERE 
    seq.cid = 208
AND
    sub.desc REGEXP '^Home pg free trail (A|B)' 
GROUP BY
    ARName,
    ARDescription,
    MessageName,
    MessageDescription,
    EventType

我发现试图用语言表达这个问题具有挑战性，如果我问的不清楚，我很抱歉。如果我可以添加更多信息，请告诉我。

经过讨论，换句话说，我的要求是:

"For each instance of 3347 get the corresponding instances of UserID and with those UserIDs the count of distinct corresponding datapoints in my.data WHERE my.my_id = 2765"

Answer 1

也许尝试将问题行更改为，选择 UserId，而不是数据:

SELECT 
    sub.name AS ARName,
    sub.desc AS ARDescription,
    m.name AS MessageName,
    m.subj AS MessageDescription,
    clk.type AS EventType,
    COUNT(DISTINCT clk.eid) AS CountAdmins,
    COUNT(DISTINCT CASE WHEN my.my_id = 3347 THEN clk.eid END) AS CountUpgradeAdmins,
    COUNT(DISTINCT CASE WHEN my.my_id = 2765 THEN my.data END) AS CountAccounts,
    COUNT(DISTINCT  my2.data ) AS CountUpgradedAccounts 
FROM
    bata.sseq seq 
INNER JOIN bata.messages m 
    ON m.id = seq.mid 
INNER JOIN bm_arc.clicks208 clk 
    ON clk.camp = seq.camp 
INNER JOIN bemails.cid cid 
    ON cid.id = clk.eid 
INNER JOIN bonfig.sub
    ON sub.id = seq.sid
LEFT JOIN bemails.my208 my 
    ON cid.id = my.eid AND (my.my_id = 3347 OR my.my_id = 2765) # only return people who upgraded and     

LEFT JOIN bemails.my208 my2 
    ON  my2.my_id = 2765 and my2.userID = my.userID and my.my_id=3347 #get the accounts that the user belongs to
WHERE 
    seq.cid = 208
    AND
    sub.desc REGEXP '^Home pg free trail (A|B)' 
GROUP BY
    ARName,
    ARDescription,
    MessageName,
    MessageDescription,
    EventType