我正在执行查询以返回存储在 sb_carts 表中的所有用户购物车。存储在 sb_carts 中的产品信息通过两个键 Product_sku 和 school_id 引用。它需要参考两者来返回具有独特库存水平等的独特产品。
当我执行以下查询时,它返回一行,我期望 3 行。
我尝试将内部联接分成两个单独的联接,但这仍然只返回 1 个结果。仅在一个键上加入可以获得所需的结果,但可能会重新调整错误的产品。左连接返回 3 行,但某些数据缺少特定于产品的数据
这是我正在做的事情的一个简化示例
SELECT sb_carts.product_sku
FROM sb_carts
INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku
AND sb_products.school_id = sb_carts.school_id
WHERE 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_sku
FROM sb_carts
INNER JOIN sb_products ON sb_products.sku = sb_carts.product_sku
AND sb_products.school_id = sb_carts.school_id
WHERE sb_carts.order_id = 0
AND sb_carts.user_id = 2
ORDER BY sb_carts.cart_id
这将返回两列(购物车 ID 和 SKU)而不是一列,并且一个购物车 ID 的所有商品将在查询中显示为连续行。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)