mysql子查询的正确使用

发布于 2024-10-12 05:07:04 字数 781 浏览 3 评论 0原文

我的数据库中有这 3 个表:product(id、sku、type、price)product_relation(parent_id、child_id)product_stock(product_id、quantity、 in_stock)。产品之间可能存在关系:X 类型的产品可以是 Y 类型的多个产品的父级(可以由其组成)(关系保存在 product_relation 表中)。在 DB 中,类型 X 的产品的数量始终设置为 0。现在是这样。我只需要获取有库存 (in_stock = true) 的 X 类型产品(skuid),并且至少其中一种孩子有 数量 > 0in_stock = true

我被困在这个问题上好几个小时了。我无法对此进行很好的查询。我实现的最接近的一个是


SELECT a.`id`, a.`sku` FROM `product` AS a 
INNER JOIN `product_stock` AS b 
ON a.`id` = b.`product_id`
INNER JOIN `product_relation` AS c 
ON c.`child_id` = b.`product_id` 
WHERE b.`in_stock` = 1 AND a.`type` = 'X'

,但效果不好,因为缺少许多项目。我不知道这是否可以仅通过连接或子查询来处理 请帮忙。

I have this 3 tables in my DB: product (id, sku, type, price), product_relation (parent_id, child_id) and product_stock (product_id, quantity, in_stock). There are possible relations between products: Product of type X can be a parent (can consists of) to several products of type Y (relation kept in product_relation table). In DB products of type X always have quantity set to 0. Now here is the thing. I need to get only products (sku and id) of type X which are in stock (in_stock = true) and at least one of their children has quantity > 0 or in_stock = true.

I'm stuck on that for several hours. I can't make a good query for this. The closest one I achieved is


SELECT a.`id`, a.`sku` FROM `product` AS a 
INNER JOIN `product_stock` AS b 
ON a.`id` = b.`product_id`
INNER JOIN `product_relation` AS c 
ON c.`child_id` = b.`product_id` 
WHERE b.`in_stock` = 1 AND a.`type` = 'X'

but it's not good, because many items are missing. I don't know if this can be handle by joins only or subqueries have
Help please.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

定格我的天空 2024-10-19 05:07:04

我不完全确定我明白你在问什么;这有帮助吗?

SELECT a.id, a.sku 
FROM product AS a 
    INNER JOIN product_stock AS b  ON a.id = b.product_id
WHERE b.in_stock = 1 AND a.type = 'X'
and exists (
    SELECT 'EXISTS'
    FROM product d
        INNER JOIN product_relation AS c ON c.child_id = d.product_id
    WHERE 
        c.product_id = a.product_id
        AND (d.quantity > 0 or d.in_stock = true)

)

I'm not totally sure I understood what you were asking; does this help?

SELECT a.id, a.sku 
FROM product AS a 
    INNER JOIN product_stock AS b  ON a.id = b.product_id
WHERE b.in_stock = 1 AND a.type = 'X'
and exists (
    SELECT 'EXISTS'
    FROM product d
        INNER JOIN product_relation AS c ON c.child_id = d.product_id
    WHERE 
        c.product_id = a.product_id
        AND (d.quantity > 0 or d.in_stock = true)

)
柳絮泡泡 2024-10-19 05:07:04

如果您不需要子查询,则不需要它。这里的优点是,如果您想知道哪些子项满足条件,您只需将其字段添加到 select 子句即可。

SELECT DISTINCT parent.`id`, parent.`sku`


FROM
`product` parent as parent
INNER JOIN `product_relation` AS c 
ON c.`parent_id` = b.`product_id` 
INNER JOIN `product_stock` AS parent_stock
ON p.`product_id` = parent_stock.`product_id`    
INNER JOIN `product` as child 
ON c.`child_id` = child.`product_id`
INNER JOIN `product_stock` AS child_stock
ON c.`product_id` = child_stock.`product_id`    

WHERE parent_stock.`in_stock` = 1
    and (child_stock.`in_stock` = 1 or child_stock.`quantity`> 0)
    and  parent.`type` = 'X'

You don't need a sub query if you don't want it. The advantage here is that if you wanted to know which children met the criteria you could just add its fields to the select clause.

SELECT DISTINCT parent.`id`, parent.`sku`


FROM
`product` parent as parent
INNER JOIN `product_relation` AS c 
ON c.`parent_id` = b.`product_id` 
INNER JOIN `product_stock` AS parent_stock
ON p.`product_id` = parent_stock.`product_id`    
INNER JOIN `product` as child 
ON c.`child_id` = child.`product_id`
INNER JOIN `product_stock` AS child_stock
ON c.`product_id` = child_stock.`product_id`    

WHERE parent_stock.`in_stock` = 1
    and (child_stock.`in_stock` = 1 or child_stock.`quantity`> 0)
    and  parent.`type` = 'X'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文