MySQL MYSQL内部通过两个键连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2462111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Inner Join two table over two keys
提问by bertsisterwanda
I am doing a query to return all users shopping carts, stored in the sb_carts table. The product information stored in sb_carts is referenced over two keys product_sku and school_id. It needs to reference both to return a unique product with unique stock levels etc.
我正在执行查询以返回存储在 sb_carts 表中的所有用户购物车。存储在 sb_carts 中的产品信息通过两个键 product_sku 和 school_id 进行引用。它需要参考两者以返回具有独特库存水平等的独特产品。
When I execute the following query it returns one row, I am expecting 3 rows. I have tried breaking the inner join into two separate joins but this still returns only 1 result. joining only on one key has the desired result, but may be retuning the wrong product. A left join returns 3 rows but some data is missing product specific
当我执行以下查询时，它返回一行，我期待 3 行。我尝试将内部连接分解为两个单独的连接，但这仍然只返回 1 个结果。仅在一个键上加入会产生预期的结果，但可能会重新调整错误的产品。左连接返回 3 行，但某些数据缺少特定于产品的数据
Here is a simplified example of what I am doing
这是我正在做的事情的简化示例
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
The Full Query looks like
完整查询看起来像
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
回答by Doug
The problem is most likely GROUP BY. This will return only one record per cart ID, even if there are multiple products in the cart. To get what you want, try this:
问题是最有可能的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
That will return two columns (the cart ID and SKU) instead of one, and all the items for the one cart ID will appear as consecutive rows in the query.
这将返回两列（购物车 ID 和 SKU）而不是一列，并且一个购物车 ID 的所有项目将在查询中显示为连续行。
回答by san983
Why are you using the Group Bystatement? If you are not using a function in the Selectlike Sum, AVG, etc makes no sense for your porpouse.
So, try running the query without the Group Bystatement :-)
你为什么用这个Group By语句？如果您没有在使用功能Select一样Sum，AVG等是没有意义的为您porpouse。因此，尝试在没有Group By语句的情况下运行查询:-)
Hope it helps!
希望能帮助到你！
Santi! :-)
桑蒂！:-)
回答by Bishwas Mishra
Mysql INNER JOIN on multiple keys:
Mysql INNER JOIN 在多个键上：
SELECT * from table1 as t1
INNER JOIN table2 as t2
ON CONCAT(t1.key1, t2.key2)=CONCAT(t2.key1, t2.key2);
What we are doing here is combining two key's values and making it look like it is one key.
我们在这里所做的是将两个键的值组合起来，使其看起来像是一个键。
