我正在为一个应用程序编写一个查询,该应用程序需要列出所有产品及其购买次数。
我想出了这个并且它有效,但我不太确定它的优化程度。由于我大量使用 ORM,我的 SQL 真的很生疏,但在这种情况下,查询是一个更优雅的解决方案。
你能发现查询有什么错误(方法明智)吗?
SELECT products.id,
products.long_name AS name,
count(oi.order_id) AS sold
FROM products
LEFT OUTER JOIN
( SELECT * FROM orderitems
INNER JOIN orders ON orderitems.order_id = orders.id
AND orders.paid = 1 ) AS oi
ON oi.product_id = products.id
GROUP BY products.id
架构(包含相关字段)如下所示:
*orders* id, paid
*orderitems* order_id, product_id
*products* id
更新
这是针对 MySQL 的
我不确定"(选择 *"...商。
这执行(总是一个好的开始),我认为相当于发布的内容。
SELECT products.id,
products.long_name AS name,
count(oi.order_id) AS sold
FROM products
LEFT OUTER JOIN
orderitems AS oi
INNER JOIN
orders
ON oi.order_id = orders.id AND orders.paid = 1
ON oi.product_id = products.id
GROUP BY products.id
这里有一个解决方案,适合我们这些筑巢障碍的人。 (当我开始嵌套连接时,我感到非常困惑)
SELECT products.id,
products.long_name AS name,
count(oi.order_id) AS sold
FROM orders
INNER JOIN orderitems AS oi ON oi.order_id = orders.id AND orders.paid = 1
RIGHT JOIN products ON oi.product_id = products.id
GROUP BY products.id
但是,我在MS SQL Server上测试了您的解决方案,Mike的解决方案和我的解决方案,并且查询计划是相同的。 我不能代表MySql,但如果MS SQL Server是可以接受的,你可能会发现所有三种解决方案的性能都是等效的。 如果是这种情况,我想您可以选择哪种解决方案对您来说最清楚。
它能给你正确的答案吗?
除了只是修改它以摆脱内部查询中的 SELECT 之外,我认为它没有任何问题。
你有"左外连接",这可能是一个性能问题,具体取决于您的数据库。上次我记得它在MySQL上引起了地狱,它在SQLite中不存在。我认为Oracle可以很好地处理它,我想DB和MSSQL也是如此。
编辑:如果我没记错的话,左外连接在MySQL上可能会慢几个数量级,但是如果我在这里过时了,请纠正我:)
未经测试的代码,但请尝试一下:
SELECT products.id,
MIN(products.long_name) AS name,
count(oi.order_id) AS sold
FROM (products
LEFT OUTER JOIN orderitemss AS oi ON oi.product_id = products.id)
INNER JOIN orders AS o ON oi.order_id = o.id
WHERE orders.paid = 1
GROUP BY products.id
我不知道左外连接是否需要括号,如果 MySQL 允许多个连接,也不知道 MIN(products.long_name) 只给出了描述,因为对于每个 products.id 你只有一个描述。
也许括号需要围绕内部连接。
这是一个子查询表单。
SELECT
p.id,
p.long_name AS name,
(SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id in
(SELECT o.id FROM Orders o WHERE o.Paid = 1 AND o.Product_id = p.id)
) as sold
FROM Products p
它的性能应大致等同于联接表单。 如果没有,请告诉我。