mysql - SQL 联合查询简化

Question

我不知道这个问题在这个论坛上是否合适。

我有一个巨大的查询:

SELECT threshold.id, brand.id, COUNT(brand.id), threshold
FROM current_stock, article, product, brand, delivery, threshold
    WHERE current_stock.article_id = article.id
    AND article.product_product_code = product.product_code
    AND product.brand_id = brand.id
    AND article.delivery_id = delivery.id
    AND delivery.store_id = 'E260'
    AND threshold.brand_id = brand.id
    GROUP BY brand.id
    HAVING COUNT(brand.id) <= threshold
UNION
SELECT threshold.id, brand.id, 0, threshold
FROM current_stock, article, product, brand, delivery, threshold
    WHERE threshold.store_id = 'E260'
    AND threshold.brand_id NOT IN (
        SELECT brand_id FROM current_stock, article, product, delivery
        WHERE current_stock.article_id = article.id
        AND article.product_product_code = product.product_code
        AND article.delivery_id = delivery.id
        AND delivery.store_id = 'E260')

而且我认为可以做得更好，但经过一整天的尝试后，我还没有找到给出相同结果的更好查询。

澄清一下，我有一个库存(包含 current_stock、文章、产品和交货)。我也有阈值。我想要的是检查每个阈值是否存在给定品牌的给定最小库存量。

我的问题是，如果某个品牌有 0 篇文章，查询的第一部分将不会关心该品牌的阈值。这就是为什么我添加了一个丑陋的联盟。

有人有更好的方法来做到这一点？

---

编辑

这是我在阅读评论和答案后所做的:

SELECT t.id, b.id, t.threshold, count(b.id) 股票

FROM threshold t
    inner join brand b on b.id = t.brand_id 
    left join product p on p.brand_id = b.id 
    inner join article a on a.product_product_code = p.product_code 
    inner join delivery d on d.id = a.delivery_id 
    inner join current_stock cs on cs.article_id = a.id

WHERE
    t.store_id = 'E260' AND
    d.store_id = 'E260'

GROUP BY b.id
HAVING stock <= t.threshold

我的问题是它没有给出所有阈值……只给出至少有一个“current_stock”的阈值。我可能不明白联接是如何工作的。

这里是一个阈值表的例子:

| id | brand_id | threshold |
-----------------------------
| 1  |   86     |     1     |
| 2  |   28     |     1     |
| 3  |   12     |     1     |

我想要的结果是:

# with 2 entries in 'current_stock' for the brand id 28, 1 for 12 and 0 for 86

| t.id | b.id | threshold | stock |
-----------------------------------
|  1   |  86  |    1      |   0   |
|  3   |  12  |    1      |   1   |

Answer 1

由于您在示例中使用了隐式联接，所以在这里猜测几个部分。显式版本看起来像这样(前提是我猜对了您加入阈值表的方式)。

SELECT
t.id,
b.id,
COUNT(b.id),
t.threshold

FROM
current_stock c
inner join article a on a.id = c.article_id
inner join delivery d on d.id = a.delivery_id
inner join product p on p.product_code = a.product_product_code
inner join brand b on b.id = p.brand_id
inner join threshold t on t.brand_id = b.id

WHERE
d.store_id = 'E260'

GROUP BY b.id
HAVING COUNT(b.id) <= t.threshold

现在要让您的结果包含没有任何“文章”的行，您可以开始将内部联接切换为左联接。但是，您不能在上面的示例中简单地使用 left outer join article...，因为 WHERE 子句中的 store_id 只会将其变回伪内部联接。

相反，您是否可以从 current_stock 加入 delivery 表的不同字段？

编辑 - 2015 年 7 月 29 日

我认为你很接近，你可能只是有一个太多的过滤器，当想要的结果表明你应该从“cs”开始计数时，你从“b”开始计数。试试这个:

SELECT t.id, b.id, t.threshold, count(cs.id) stock

FROM
threshold t
inner join brand b on b.id = t.brand_id
inner join product p on p.brand_id = b.id
inner join article a on a.product_product_code = p.product_code
left outer join delivery d on d.store_id = t.store_id
left outer join current_stock cs on cs.article_id = a.id

WHERE
t.store_id = 'E260'

GROUP BY b.id
HAVING stock <= t.threshold