mysql - 如何在不同的表中查找不匹配的行

Question

我有一个包含 5 个表的数据库。所有表中的一个字段是相同的。现在，由于一些数据输入错误，一些行不匹配。我如何找到哪些不匹配以及在哪些表中。表结构如下:-

stockmaster(code,description)
stockquantity(code,quantity)
stockbuffer(code,buffer)
stockweights(code,weight)
stockorders(code,orderqty)

我想找出所有表中哪些代码不匹配。任何帮助将不胜感激。

我使用以下查询来查找匹配的查询。

SELECT stockmaster.code as a, stockquantity.code as b, stockbuffer.code as c, stockweights.code as d, stockorders.code as e FROM stockmaster JOIN stockquantity ON stockquantity.code = stockmaster.code  and so on...

上面给了我匹配的。

Answer 1

要获取表上缺少记录的代码列表(假设这里存在一对一关系)，以及说明在哪个表上缺少记录的注释，应该这样做

SELECT Codes.code,
CONCAT(CASE WHEN stockmaster.code IS NULL THEN 'Missing from stockmaster. ' ELSE '' END
CASE WHEN stockquantity.code IS NULL THEN 'Missing from stockquantity. ' ELSE '' END
CASE WHEN stockbuffer.code IS NULL THEN 'Missing from stockbuffer. ' ELSE '' END
CASE WHEN stockweights.code IS NULL THEN 'Missing from stockweights. ' ELSE '' END
CASE WHEN stockorders.code IS NULL THEN 'Missing from stockorders. ' ELSE '' END)
FROM (SELECT code FROM stockmaster
UNION
SELECT code FROM stockquantity
UNION
SELECT code FROM stockbuffer
UNION
SELECT code FROM stockweights
UNION
SELECT code FROM stockorders) Codes
LEFT OUTER JOIN stockmaster ON stockmaster.code = Codes.code
LEFT OUTER JOIN stockquantity ON stockquantity.code = Codes.code
LEFT OUTER JOIN stockbuffer ON stockbuffer.code = Codes.code
LEFT OUTER JOIN stockweights ON stockweights.code = Codes.code
LEFT OUTER JOIN stockorders ON stockorders.code = Codes.code
WHERE stockmaster.code IS NULL
OR stockquantity.code IS NULL
OR stockbuffer.code IS NULL
OR stockweights.code IS NULL
OR stockorders.code