是否可以通过分组多个列来获取一行(Is it possible to get one row by grouping more than one column)

我有一个查询如下。数据库来自http://www.w3schools.com/sql/default.asp

SELECT count(distinct C.CustomerID),C.Country FROM Customers C inner join Orders O on C.CustomerID = O.CustomerID inner join OrderDetails D on O.OrderID = D.OrderID inner join Products P on D.ProductID = P.ProductID group by C.Country,P.CategoryID order by C.Country

这是上面的结果。

但我想通过计算CustomerID来获得每个国家的一行（如下图所示），其中任何CustomerID在同一个国家/地区并且具有相同的CategoryID。所以我必须按2列分组。有什么办法吗？请你帮我一下吗？

谢谢。

I have a query as below. DB from http://www.w3schools.com/sql/default.asp

SELECT count(distinct C.CustomerID),C.Country FROM Customers C inner join Orders O on C.CustomerID = O.CustomerID inner join OrderDetails D on O.OrderID = D.OrderID inner join Products P on D.ProductID = P.ProductID group by C.Country,P.CategoryID order by C.Country

Here is the result from above.

But I want to get one row per country(as below pic) by counting CustomerIDs where any CustomerIDs are in the same country and have a same CategoryID as well. So I have to group by 2 columns. Is there any way to do it? Could you please kindly suggest me?

Thank you.

最满意答案

这很简单。只需删除GROUP BY子句中的P.CategoryID 。

SELECT COUNT(DISTINCT C.CustomerID), C.Country FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID INNER JOIN OrderDetails D ON O.OrderID = D.OrderID INNER JOIN Products P ON D.ProductID = P.ProductID GROUP BY C.Country ORDER BY C.Country;

更新

在您的评论之后，这应该是正确的方法：

SELECT T.Country, SUM(T.Cnt) FROM ( SELECT COUNT(DISTINCT C.CustomerID) AS Cnt, C.Country FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID INNER JOIN OrderDetails D ON O.OrderID = D.OrderID INNER JOIN Products P ON D.ProductID = P.ProductID GROUP BY C.Country, P.CategoryID ) AS T GROUP BY T.Country ORDER BY T.Country;

That's quite simple. Just remove P.CategoryID in your GROUP BY clause.

SELECT COUNT(DISTINCT C.CustomerID), C.Country FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID INNER JOIN OrderDetails D ON O.OrderID = D.OrderID INNER JOIN Products P ON D.ProductID = P.ProductID GROUP BY C.Country ORDER BY C.Country;

Update

Following your comment, this should be correct approach then:

SELECT T.Country, SUM(T.Cnt) FROM ( SELECT COUNT(DISTINCT C.CustomerID) AS Cnt, C.Country FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID INNER JOIN OrderDetails D ON O.OrderID = D.OrderID INNER JOIN Products P ON D.ProductID = P.ProductID GROUP BY C.Country, P.CategoryID ) AS T GROUP BY T.Country ORDER BY T.Country;

更多推荐