gpt4 book ai didi

MYSQL 内部通过两个键连接两个表

转载 作者:行者123 更新时间:2023-11-29 02:37:46 25 4
gpt4 key购买 nike

我正在查询以返回存储在 sb_carts 表中的所有用户购物车。存储在 sb_carts 中的产品信息通过两个键 product_sku 和 school_id 引用。它需要引用两者以返回具有独特库存水平等的独特产品。

当我执行以下查询时,它返回一行,我期望有 3 行。我曾尝试将内部联接分成两个单独的联接,但这仍然只返回 1 个结果。仅加入一个键可获得预期的结果,但可能会重新调整错误的产品。左联接返回 3 行,但某些数据缺少特定于产品的数据

这是我正在做的一个简单的例子

SELECT sb_carts.product_skuFROM sb_carts INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku AND sb_products.school_id = sb_carts.school_idWHERE sb_carts.order_id = 0 AND sb_carts.user_id = 2 GROUP BY sb_carts.cart_id

完整的查询看起来像

SELECT COUNT(DISTINCT sb_carts.cart_id) as quantity, sb_carts.* FROM sb_carts INNER JOIN sb_children ON sb_children.child_id = sb_carts.child_id INNER JOIN sb_school_entities ON sb_school_entities.school_id   = sb_children.school_id INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku AND sb_products.school_id = sb_carts.school_id LEFT JOIN sb_houses ON sb_children.house_id = sb_houses.id LEFT JOIN sb_refund_cart ON sb_carts.cart_id = sb_refund_cart.cart_id WHERE sb_carts.order_id = 0 AND sb_carts.user_id = 2 GROUP BY sb_carts.child_id, sb_carts.product_sku, sb_carts.school_id ORDER BY sb_children.dob_year, sb_children.dob_month, sb_children.dob_day ASC

最佳答案

问题很可能是 GROUP BY。这将只返回每个购物车 ID 的一条记录,即使购物车中有多个产品。要得到你想要的,试试这个:

SELECT sb_carts.cart_id, sb_carts.product_skuFROM sb_carts INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku AND sb_products.school_id = sb_carts.school_idWHERE sb_carts.order_id = 0 AND sb_carts.user_id = 2 ORDER BY sb_carts.cart_id

这将返回两列(购物车 ID 和 SKU)而不是一列,并且一个购物车 ID 的所有商品将在查询中显示为连续的行。

关于MYSQL 内部通过两个键连接两个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2462111/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com