MySQL查询多个表 [英] MySQL Querying Multiple Tables

问题描述

感谢您看我的问题！我一直试图找出一个查询来执行以下操作，但没有成功.我真的很感激任何帮助.提前致谢:-)

Thank you for taking a look at my question! I've been trying to figure out a single query to do the following but have been unsuccessful. I would truly appreciate any help. Thank you in advance :-)

我正在为我的电子商务商店制作一个管理页面，显示过去 X 天内未售出的产品.需要用到三个表...

I am making an admin page for my e-commerce store that shows products that haven't sold in the last X days. There are three tables that need to be used...

Table: products
Column: product_id (int) 

此表/列包含商店中的所有产品

This table/column contains all the products in the store

 Table: orders
 Columns: order_id (int), date_ordered (datetime)

此表包含由 order_id 标识的所有订单以及订购日期 (date_ordered).

This table contains all the orders which are identified by order_id and the date for which they were ordered (date_ordered).

 Table: order_products
 Column: order_id (int), product_id (int) 

此表包含所有已订购产品 (product_id) 和相应订单 (order_id) 的完整列表.

This table contains a complete listing of all products ordered (product_id) and the corresponding order (order_id).

因此，我试图找出的查询将使用表 orders 和 order_products 中的 order_id 来确定过去 X 天内哪些产品已售出...然后从产品表中返回尚未售出的任何 products_id在过去的 X 天内.

So, the query I'm trying to figure out would use use the order_id in tables orders and order_products to determine which products have sold in the last X days... Then return any products_id from the products table which have not sold in the last X days.

有什么建议吗?任何帮助将不胜感激！谢谢:-)

Any suggestions? Any help would be very appreciated! Thank you :-)

推荐答案

好吧，虽然我部分同意您应该四处看看并了解有关左连接的更多信息，但正确回答这个问题也有一些技巧，可能会迷失在初学者身上.我会继续帮助您回答这个问题，但我建议您了解有关联接的更多信息.

Okay so while I agree in part that you should do some poking around and learn more about left joins, there is also some trickiness to answering this question correctly that might be lost on a beginner. I'm gonna go ahead and help you answer it, but I would recommend learning more about joins.

我的确切查询将取决于可用索引，但它很可能类似于以下内容:

My exact query would depend on the available indices, but it very likely resemble something like this:

SELECT a.* 
FROM products AS a
LEFT JOIN (
SELECT product_id FROM order_products as b 
INNER JOIN orders AS c
ON b.order_id = c.order_id
WHERE c.date_ordered >= date_sub(c.date_ordered, INTERVAL 7 day)
GROUP BY product_id 
) AS d
ON a.product_id = d.product_id 
WHERE d.product_id IS NULL

我正在做的是编写一个将订单和订单产品连接在一起的子查询，其中 date_ordered 落在某个日期范围内(我建议在此处了解 date_sub 函数:http://www.w3schools.com/sql/func_date_sub.asp 并做一些快速的 SELECT date_sub(date_ordered, INTERVAL)X DAY) FROM 订单查询，以确保您在实践中了解此计算的工作原理.

What I'm doing is I'm writing a subquery that joins orders and orders products together, where date_ordered falls within a certain date range (I would recommend learning about the date_sub function here: http://www.w3schools.com/sql/func_date_sub.asp and also do a few quick SELECT date_sub(date_ordered, INTERVAL X DAY) FROM orders queries to make sure you understand how this calculation works, in practice.

现在，我获得了过去 X 天的订单列表(上面查询中的 7 天)，并将其与订单产品表结合以获取订购的产品.在这里，我想基本上删除我的产品.Product_id = 300 可能已被订购 70 次.Product_id = 200 可能已被订购 50 次.无论是哪种情况，我都不想将 70 条记录和 50 条记录加入到我的产品表中，产品 ID 为 300 和 200，因此我对它们进行了去重.最后一个 GROUP BY 语句就是这样做的.它在功能上与编写 DISTINCT 相同(尽管在某些情况下这些计算方式可能存在细微差别，但这些情况似乎都不适用于这里...如果您更清楚，请使用 DISTINCT)

Now, I get my list of orders for the last X days (7 in the query above) and I join it with the orders product table to get the products that were ordered. Here, I want to dedup my products, basically. Product_id = 300 may have been ordered 70 times. Product_id = 200 may have been ordered 50 times. Whatever the case may be, I don't want to join 70 records and 50 records to my product table for product ids 300 and 200, so I dedup them. That last GROUP BY statement does that. It's functionally the same thing as writing DISTINCT (although there can be minor differences in how these are computed in certain circumstances, none of those circumstances seem to apply here... use DISTINCT if that's clearer for you)

获得过去 X 天内订购的唯一产品 ID 的列表后，我会将其加入我的产品表中.在这里，我使用左连接.就像上面提到的评论一样，您需要非常仔细地研究连接的概念.这样做，如果你还没有.

Once I have my list of unique product ids that were ordered in the past X days, I join that with my product table. Here, I use a left join. Like the comments noted above, you'll want to look into the notion of joins pretty carefully. Do that, if you haven't already.

最后，我应用了一个 WHERE 过滤器，上面写着WHERE d.product_id IS NULL".这是在说，好吧，如果 product_id = Y 在过去 X 天内被订购，那么它将成功加入我的产品表 a.product_id = d.product_id.如果它没有被订购，那么 a.product_id = d.product_id.product_id 将存在于我的结果集中，但 d.product_id 不会.也就是说，d.product_id 将为空."

Last, I apply a WHERE filter that says "WHERE d.product_id IS NULL." What this is doing is saying, "okay, if product_id = Y was ordered in the past X days, then it will join to my products table successfully with a.product_id = d.product_id. If it wasn't ordered, then a.product_id will exist in my result set, but d.product_id won't. That is, d.product_id will be null."

最后一个转折可能是不明显/不突出的部分.

That last twist may be the part that's not apparent / standing out.

希望这会有所帮助.

这篇关于MySQL查询多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！