mysql - mysql找到丢失的项目

我有一个具有以下结构的表

mysql> describe stock_prices;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| code                | varchar(16) | YES  | MUL | NULL    |                |
| pricelist           | varchar(10) | YES  | MUL | NULL    |                |
| settlement_discount | tinyint(1)  | YES  |     | NULL    |                |
| overal_discount     | tinyint(1)  | YES  |     | NULL    |                |
| sale                | tinyint(1)  | YES  |     | NULL    |                |
| price_blob          | longtext    | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

当我运行此查询

mysql> SELECT pricelist, count(pricelist) as dup from stock_prices group by pricelist having dup>1 order by dup;
+-----------+------+
| pricelist | dup  |
+-----------+------+
| GMBH      | 1843 |
| DISTCART  | 2241 |
| DISTSTD   | 2241 |
| CART      | 2242 |
| USSD      | 2242 |
| SPCA      | 2242 |
| SPCB      | 2242 |
| SPCC      | 2242 |
| EUCN      | 2242 |
| STD       | 2242 |
| EUSD      | 2242 |
| USCN      | 2242 |
+-----------+------+
12 rows in set (0.03 sec)

所有价目表项都应具有相同的值，但是GMBH少了399，而DISTCART和DISTSTD少了1。

基本上，我有code没有价目表条目。

当我跑步时：

mysql> SELECT code, count(code) as dup from stock_prices group by code having dup>1 order by dup;
+-------------+-----+
| code        | dup |
+-------------+-----+
| XN44-CH2    |   9 |
| XN23-MGY1   |  11 |
| XN24-CH2    |  11 |
| XN25-VWH1   |  11 |
| XN36-BL2    |  11 |
| XN36-CH3    |  11 |
| XN37-BL3    |  11 |
| XN38-BC3    |  11 |
| XN38-CE3    |  11 |
....

因此在这种情况下XN44-CH2缺少3个代码，而XN23-MGY1缺少1个代码

mysql> SELECT COUNT(pricelist) FROM stock_prices WHERE pricelist = 'GMBH';
+------------------+
| COUNT(pricelist) |
+------------------+
|             1843 |
+------------------+
1 row in set (0.00 sec)

找出每个缺失的价目表的正确方法是什么？

任何建议，不胜感激。

最佳答案

假设所有价格表都有一个参考表，所有代码都有一个参考表，则可以在标准SQL中执行以下操作：

SELECT
  p.pricelist,
  c.code
FROM
  pricelists AS p
CROSS JOIN
  codes AS c

EXCEPT

SELECT
  pricelist,
  code
FROM
  stock_prices
;

也就是说，获取现有价格表和代码的所有组合，然后减去stock_prices中存在的价格和代码。结果将是缺少对。

由于MySQL不支持EXCEPT，因此可以使用LEFT JOIN实现相同的逻辑：

SELECT
  p.pricelist,
  c.code
FROM
  pricelists AS p
CROSS JOIN
  codes AS c
LEFT JOIN
  stock_prices AS s ON p.pricelist = s.pricelist
                   AND c.code = s.code
WHERE s.id IS NULL
;

如果没有这些参考表，则可以通过以下方式将它们替换为派生表：

pricelists  ==>  (SELECT DISTINCT pricelist FROM stock_prices)

codes  ==>  (SELECT DISTINCT code FROM stock_prices)

然后查询将如下所示：

SELECT
  p.pricelist,
  c.code
FROM
  (SELECT DISTINCT pricelist FROM stock_prices) AS p
CROSS JOIN
  (SELECT DISTINCT code FROM stock_prices) AS c
LEFT JOIN
  stock_prices AS s ON p.pricelist = s.pricelist
                   AND c.code = s.code
WHERE s.id IS NULL
;