mysql错误: use result of innerjoin into new query

Question

我有 2 个要合并的 sql 字符串:

• 第一个表有一个产品的所有数据
• 第二个表(inner join)包含具有团体价格的产品的所有数据。

但我得到不同的结果

测试搜索“sku”

SELECT sku
FROM  `c3eiwitlive`.`catalog_product_entity` 
WHERE (
 `entity_id` LIKE  '%17086%'
)

在group_price表上直接sql

结果269条记录

SELECT SKU, value
FROM  `c3eiwitlive`.`catalog_product_entity_group_price` AS T1
INNER JOIN
`c3eiwitlive`.`catalog_product_entity` AS T2 
ON T1.`entity_id` = T2.`entity_id`;

组合sql返回较少的记录

结果234条记录

SELECT name
FROM  `c3eiwitlive`.`catalog_product_flat_1` AS T1
WHERE T1.`sku` 
IN (
    SELECT sku
    FROM  `c3eiwitlive`.`catalog_product_entity_group_price` AS T1
    INNER JOIN  `c3eiwitlive`.`catalog_product_entity` AS T2 ON T1.`entity_id` = T2.`entity_id`
)

Answer 1

在我看来，'catalog_product_flat_1' 表中缺少一些 sku 条目。为什么不反转查询来识别缺失的行？

SELECT sku
FROM  `c3eiwitlive`.`catalog_product_entity_group_price` AS T1
INNER JOIN  `c3eiwitlive`.`catalog_product_entity` AS T2 ON T1.`entity_id` = T2.`entity_id`
WHERE sku 
NOT IN (
      SELECT sku from `c3eiwitlive`.`catalog_product_flat_1`
)