mysql - SQL : easiest way to show missing data

Question

我正在使用销售工具。我们有一张客户表，还有一张元素表。但是，只有当定价表中存在该客户和该商品的记录时，才允许该客户购买产品。有时该表中会缺少记录，并且某些客户无法订购某些商品。

我有一个客户列表(CustA、CustB、CustC)和一个项目列表(Item1、Item2、Item3)。我想看看这九种可能性的定价表记录是否不存在。我可以编写一个查询来显示定价记录存在的位置，但如果我只获得七个记录，我必须根据我应该看到的内容手动检查每种可能性以查找丢失的记录。

Select PricingContractID, ItemKey 
from PricingContracts 
where PricingContractID in (CustA, CustB, CustC) 
   and ItemKey in (Item1, Item2, Item3)

结果:

| CustA | Item1 |   
| CustB | Item1 |   
| CustA | Item3 |   
| CustA | Item2 |   
| CustB | Item3 |  
| CustC | Item2 |  
| CustC | Item3 |

我想要查询以显示哪些记录不存在。

| CustB | Item2 |  
| CustC | Item1 |  

特别是，我希望将其放在一个数组中，其中一个轴为商品编号，另一轴为客户编号，以及一些指示记录存在的指示符。

|   •   | Item1 | Item2 | Item3 |  
| CustA |   Y   |   Y   |   Y   |   
| CustB |   Y   |       |   Y   |   
| CustC |       |   Y   |   Y   |   

Answer 1

首先构建一个查询来选择所有可能的组合，然后在链接表上运行 LEFT JOIN。当您有 NULL 值时，您知道链接表中没有这样的行，并且可以读取列值。

链接表上没有JOIN的查询:

SELECT
    item.name,
    customer.name
FROM
    item
JOIN
    customer;

+------+------+
| name | name |
+------+------+
| a1   | b4   |
| a2   | b4   |
| a3   | b4   |
| a1   | b5   |
| a2   | b5   |
| a3   | b5   |
| a1   | b6   |
| a2   | b6   |
| a3   | b6   |
+------+------+

然后在链接表上添加 JOIN:

SELECT
    item.name,
    customer.name,
    link.itemName,
    link.customerName
FROM
    item
JOIN
    customer
LEFT JOIN
    link ON item.name = link.itemName AND
            customer.name = link.customerName;

+------+------+----------+--------------+
| name | name | itemName | customerName |
+------+------+----------+--------------+
| a1   | b5   | a1       | b5           |
| a2   | b5   | a2       | b5           |
| a2   | b6   | a2       | b6           |
| a3   | b4   | a3       | b4           |
| a1   | b4   | NULL     | NULL         |
| a2   | b4   | NULL     | NULL         |
| a3   | b5   | NULL     | NULL         |
| a1   | b6   | NULL     | NULL         |
| a3   | b6   | NULL     | NULL         |
+------+------+----------+--------------+

然后简单地过滤你想要的行:

SELECT
    item.name,
    customer.name,
    link.itemName,
    link.customerName
FROM
    item
JOIN
    customer
LEFT JOIN
    link ON item.name = link.itemName AND
            customer.name = link.customerName
WHERE
    link.itemName IS NULL;

+------+------+----------+--------------+
| name | name | itemName | customerName |
+------+------+----------+--------------+
| a1   | b4   | NULL     | NULL         |
| a2   | b4   | NULL     | NULL         |
| a3   | b5   | NULL     | NULL         |
| a1   | b6   | NULL     | NULL         |
| a3   | b6   | NULL     | NULL         |
+------+------+----------+--------------+