delatbabel's questions

我有 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

我敢肯定有人以前一定遇到过这个或类似的问题，但我在谷歌或 DBA 交换搜索中找不到任何东西。

我有一个存储键/值对的表。表中还有 2 个限制列，host 和 environment，都可以为 null。限制表的工作方式是，如果主机列中有条目，则键/值适用于该主机，类似地，如果环境列中有条目，则该条目仅对该环境有效。如果使用主机或环境查找键/值，则有限值优先于主机=NULL 或环境=NULL 的值。此外，主机优先于环境。

例如，这里有一些表条目：

id      env     host     key    value
1       NULL    NULL     fruit  apple
2       prod    NULL     fruit  banana
3       prod    www      fruit  mango
4       test    NULL     fruit  peach
5       NULL    test     fruit  orange

鉴于该示例数据，我所追求的是一个 SQL 语句（或多个语句），它将根据这些条件返回这些数据：

env     host   key    data returned
NULL    NULL   fruit  1, fruit, apple
prod    NULL   fruit  2, fruit, banana
NULL    www    fruit  3, fruit, mango
junk    junk   fruit  1, fruit, apple  // NULL/NULL values are used as a fallback
junk    www    fruit  1, fruit, apple  // no match, fallback
prod    test   fruit  5, fruit, orange // host=test takes precedence

那有意义吗？

我可以将搜索分解为多个选择，尝试每一个，检查我是否命中等等，但我希望尽可能高效地实现这一点。

理想情况下，如果以后添加更多限制器（例如组、命名空间等），我希望搜索类型可以轻松扩展。

任何人？