查找sql中不常用的用户

Question

我有5个表。我想在表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的用户。

演示

如果您还想要，比如说，在任何其他表中都不存在的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)

演示

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