主页 / dba / 问题 / 181502
Accepted
delatbabel
Asked: 2017-07-22 21:16:39 +0800 CST

根据A中的属性从A左连接B中获取所有行

  • 772

我有 2 个表products和stores，中间有一个名为 的数据透视表product_store。

这是表结构的相关部分（来自 MySQL）：

产品

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`global` tinyint(1) NOT NULL DEFAULT '0',

商店

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,

产品商店

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`store_id` int(10) unsigned NOT NULL,
`show` tinyint(1) NOT NULL,

globalvs标志的逻辑show如下：

  • 如果global标志为 1，则假设该产品在每个商店中，除非有== 0的product_store条目。show
  • 如果global标志为 0，则假设该产品仅在有== 1product_store条目的商店中销售。show

也就是说，global== 1 表示在任何地方都展示该产品，show== 1 表示在本店展示该产品，show== 0 表示不在本店展示该产品，并show优先于global。

我想要做的是根据已知的 store_id 获取产品，其中：

  • products.global == 1 并且 product_id 和 store_id 没有 product_store 条目
  • 或者
  • product_id 有一个 product_store 条目，store_id AND product_store.show == 1

目前我有这个查询，带有 aGROUP BY和 a 嵌套SELECT，但它从数据库中提取了许多记录，然后由 the 合并，GROUP BY而且速度很慢。我认为必须有更快的东西，没有嵌套选择。

SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
    FROM products
    LEFT JOIN product_store ON products.id=product_store.product_id
    WHERE ((products.global=1 AND
          ((SELECT COUNT(*) FROM product_store WHERE product_store.store_id=226 AND product_store.product_id=products.id) = 0)
         )
        OR (product_store.store_id=226 AND product_store.show=1)
        )
    GROUP BY products.id
    ;

226是本例中的商店 ID，我一次只需要为一个商店运行此查询，因此可以将其视为常量。

请注意，如果我使用 进行上述查询，GROUP BY那么我会得到大约 14 条正确的记录（当我自己进行计数时）。系统有几千个产品和几百个商店，因此product_store连接表相当大。如果我在没有 的情况下执行上述查询，GROUP BY那么我将获得 ~ 1500 条记录，这些记录似乎是符合该LEFT JOIN子句的所有产品/产品商店组合的列表。

只是为了重新迭代，上面的查询工作正常（尽管在 MySQL 版本 > 5.7.5 中会有问题GROUP BY），但它似乎效率低下，我正试图找出更好更快的东西。

有关表格内容的更多详细信息

这里有一些关于相关产品和商店等的快速查询。首先显示所有相关匹配的产品——共有 15 个，所有这些产品的 global=1。

SELECT products.id, MD5(products.name), products.global
    FROM products
    -- .. omitted some classification information here
    ;

+-----+----------------------------------+--------+
| id  | md5(products.name)               | global |
+-----+----------------------------------+--------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |
| 591 | 7ba5549546ce20e1dee02c7f6454b16f |      1 |
| 558 | 7b66d8dce0c112b3966d1940de44f9ad |      1 |
| 556 | bd3d39b9f5194184ea0853c70d7faa07 |      1 |
| 590 | ef6eccbd3795fbe70890220c09ed880d |      1 |
| 559 | b763e2b9ba305538b497da97fba066ca |      1 |
| 593 | e26744d617ed12ebffea4b996fecbbef |      1 |
| 594 | 06ba8ab6649c83577db053e4a89c1655 |      1 |
| 596 | 79f9241643e84e2a27bfc0b260432e82 |      1 |
| 595 | 85fe772dc61d27292c09a7604cf76dd7 |      1 |
| 597 | fd6c21db00e1b679c0d0d2ef8f3f6e00 |      1 |
| 560 | c8056818e3ed5885188d78a9440fa77c |      1 |
| 561 | 33d79c194e39e8e84cf743eefcd909df |      1 |
| 562 | 1c74d85823e509ab4b09e82314b09604 |      1 |
| 557 | b6a8027035b63bd4d4bd21169434426d |      1 |
+-----+----------------------------------+--------+

现在查看与 product_store 表的连接。store_id 226 只有一个条目，它的 show=0。

SELECT products.id, MD5(products.name), products.global, product_store.show
    FROM products
    INNER JOIN product_store ON products.id=product_store.product_id
    -- omitted the classification information as per the above
    WHERE product_store.store_id=226
    ;

+-----+----------------------------------+--------+------+
| id  | MD5(products.name)               | global | show |
+-----+----------------------------------+--------+------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |    0 |
+-----+----------------------------------+--------+------+

...因此，我需要能够构建一个查询，该查询选择 global=1 的所有 15 种产品，除了 product_store.show=0 的产品，因此我需要剩余 14 种产品的集合。

UNION 选择会以某种方式工作吗？还是某种形式的相交？

示例：http ://sqlfiddle.com/#!9/1342cf/5

mysql performance

1 个回答

  1. Best Answer

    根据您的示例，这似乎是一个等效查询：

    SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
FROM products
LEFT JOIN product_store
  ON products.id=product_store.product_id
 AND product_store.store_id=226
WHERE -- there is NO entry in product_store AND products.global=1
      (products.global=1 AND product_store.show IS NULL)
      -- there is an entry in product_store with show=1 
   OR (products.global=0 AND product_store.show = 1)

    DISTINCT不应该是必要的。

    我将此添加到您的小提琴中

    ON在vs.中放置条件WHERE对于外部连接至关重要。这ON只是一个从不过滤行但可能在内部表中创建 NULL 的条件。在联接之后WHERE应用，在这里它过滤（当应用于内部表的列时，外部联接创建的所有 NULL 都将被再次删除，除非您添加）。OR col IS NULL

    • 2

相关问题