mysql - 即使不在联合表内也返回结果

Question

有人可以帮我完成这个任务吗？假设我有 2 张 table :

_表 1“发布商”:ASIN、价格、publisher_ID

_表 2“违规行为”:ASIN、publisher_ID、Previous_Violations(是/否)、ID

现在我有了这个

    SET @PUB = "

    'A','B','C','D',

    " 
    SELECT p.publisher_ID, count(distinct v.id) as "Number of Violations"
    FROM Publisher p
    LEFT JOIN Violations v
    ON p.publisher_ID = v.publisher_ID
    and p.Previous_Violations = 'Y'
    
    WHERE 
    p.publisher_ID IN (",@PUB,")
    GROUP BY p.publisher_ID

我想查看 @PUB 列表中所有发布商的历史违规记录。所以会有4种情况:

1. @PUB 中的所有发布商都存在这两个表
2. 表 1 中存在@PUB 中的发布商，但表 2 中不存在。
3. 表 2 中存在@PUB 中的发布商，但表 1 中不存在。
4. @PUB 中存在两个表中都不存在的发布商。

为了澄清，假设酒吧 A 和 B 都存在于两个表中，C 存在于表 1(而不是 2)中，D 存在于表 2(但不存在 1)中，而 E 都不存在于其中。

现在我上面的查询仅给出(由于左连接)

A 3

B 2

C 4

但我希望它显示:

A 3

B 2

C 4

D 6

E Null

Answer 1

如果您没有很大的列表，您可以尝试以下操作，您也许可以创建一个为您生成查询的过程

(
    SELECT p.publisher_ID, sum(IF(v.Previous_Violations = 'Y', 1, 0)) AS "Number of Violations"
    FROM (SELECT 'A' AS publisher_ID) AS p LEFT JOIN Violations v USING(publisher_ID) 
    GROUP BY p.publisher_ID
)
UNION
(
     SELECT p.publisher_ID, sum(IF(v.Previous_Violations = 'Y', 1, 0)) AS "Number of Violations"
    FROM (SELECT 'B' AS publisher_ID) AS p LEFT JOIN Violations v USING(publisher_ID) 
    GROUP BY p.publisher_ID)
UNION
(
     SELECT p.publisher_ID, sum(IF(v.Previous_Violations = 'Y', 1, 0)) AS "Number of Violations"
    FROM (SELECT 'C' AS publisher_ID) AS p LEFT JOIN Violations v USING(publisher_ID) 
    GROUP BY p.publisher_ID)
UNION
(
     SELECT p.publisher_ID, sum(IF(v.Previous_Violations = 'Y', 1, 0)) AS "Number of Violations"
    FROM (SELECT 'D' AS publisher_ID) AS p LEFT JOIN Violations v USING(publisher_ID) 
    GROUP BY p.publisher_ID
)
UNION
(
     SELECT p.publisher_ID, sum(IF(v.Previous_Violations = 'Y', 1, 0)) AS "Number of Violations"
    FROM (SELECT 'E' AS publisher_ID) AS p LEFT JOIN Violations v USING(publisher_ID) 
    GROUP BY p.publisher_ID
)