mysql - sql中查找不常见用户

Question

我有 5 张 table 。我想获取表 1 中不在表 2、表 3、表 4 和表 5 中的特定用户。有人可以帮我吗:)

table1(userid,discount)
table2(userid,discount)
table3(userid,discount)
table4(userid,discount)
table5(userid,discount)

Answer 1

以下查询:

SELECT userid
FROM table1 AS t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM (
    SELECT userid
    FROM table2
    UNION 
    SELECT userid
    FROM table3
    UNION 
    SELECT userid
    FROM table4
    UNION 
    SELECT userid
    FROM table5) AS t2
  WHERE t1.userid = t2.userid)

返回 table1 中不存在于任何其他表中的所有用户。

Demo here

如果您还想要 table2 中不存在于任何其他表中的所有用户，那么您可以修改上述查询，以便从 table2 返回用户 并在这两个查询之间执行 UNION:

SELECT userid
FROM table1 AS t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM (
    SELECT userid
    FROM table2
    UNION 
    SELECT userid
    FROM table3
    UNION 
    SELECT userid
    FROM table4
    UNION 
    SELECT userid
    FROM table5) AS t2
  WHERE t1.userid = t2.userid)

UNION ALL

SELECT userid
FROM table2 AS t1
WHERE NOT EXISTS (
  SELECT 1 
  FROM (
    SELECT userid
    FROM table1
    UNION 
    SELECT userid
    FROM table3
    UNION 
    SELECT userid
    FROM table4
    UNION 
    SELECT userid
    FROM table5) AS t2
  WHERE t1.userid = t2.userid)

Demo here

上面的内容可以很容易地扩展，以便合并所有表中的用户。但是，我不得不承认，它变得相当冗长!