SQL，W3school实践问题-找到法国客户订购最多的产品

Question

在W3school上做练习题-数据库在这里：https://www.w3schools.com/SQL/TRYSQL.ASP?FILENAME=TRYSQL_SELECT_ALL

我正试图退回法国国家客户订购最多的产品(见表OrderDetails((Country见表Customers(。要做到这一点，我首先必须跨3个表进行联接：首先在OrderID上联接OrderDetails和Orders，然后在CustomerID上联接合并后的表和Customers。问题是，现在我有了桌子，我似乎不知道如何退回法国客户订购最多的ProductID。。以下代码：

SELECT
c.customer_id,
SUM(od.Quantity) AS totalQuantity
FROM (OrderDetails od LEFT JOIN Orders o ON od.OrderID = o.OrderID)
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY od.ProductID
ORDER BY totalQuantity DESC
LIMIT 1;

返回错误：

Syntax error in ORDER BY clause

关于我在这里做错了什么，有什么建议吗？我所做的是不是即将退回法国客户订购最多的ProductID？

Answer 1

LIMIT不是Access SQL语法，所以请尝试：

SELECT TOP 1
od.ProductID,
SUM(od.Quantity) AS totalQuantity
FROM 
(OrderDetails AS od 
LEFT JOIN 
Orders AS o ON od.OrderID = o.OrderID)
LEFT JOIN 
Customers AS c ON o.CustomerID = c.CustomerID
GROUP BY 
od.ProductID
ORDER BY 
SUM(od.Quantity) DESC;

Answer 2

W3Schools正在使用连接到SqLite 3 的WebSQL

你可以查看它的版本：

SELECT sqlite_version();

问题中的查询不起作用，因为它选择了c.customer_id，应该是c.CustomerID。

此查询将返回具有法国最独特客户的产品。

国家/地区取自客户表
"产品"表仅用于获取产品名称。

SELECT 
od.ProductID
, p.ProductName
, COUNT(DISTINCT o.OrderID) AS TotalOrders
, COUNT(DISTINCT c.CustomerID) AS TotalCustomers
, SUM(od.Quantity) AS TotalQuantity
FROM "OrderDetails" AS od
INNER JOIN "Orders" AS o ON o.OrderID = od.OrderID
INNER JOIN "Customers" AS c ON c.CustomerID = o.CustomerID
INNER JOIN "Products" AS p ON p.ProductID = od.ProductID
WHERE c.Country = 'France' 
GROUP BY od.ProductID, p.ProductName
ORDER BY TotalCustomers DESC
LIMIT 1

这是一个肉饼。

Answer 3

这就是你的意思吗？

SELECT
c.CustomerID, c.Country,
SUM(od.Quantity) AS totalQuantity
FROM Customers c
LEFT JOIN Orders o ON (c.CustomerID = o.CustomerID)
LEFT JOIN OrderDetails od ON (od.OrderID = o.OrderID)
where c.Country = 'France'
GROUP BY c.CustomerID, c.Country
ORDER BY totalQuantity DESC;

这将输出法国客户的订单数量