从表格中选择多列，但按一个分组

Question

表名为“OrderDetails”，下面是列名:

OrderDetailID || ProductID || ProductName || OrderQuantity

我试图选择多列并按ProductID分组，同时对OrderQuantity求和。

 Select ProductID,ProductName,OrderQuantity Sum(OrderQuantity)
 from OrderDetails Group By ProductID

当然，这段代码会报错。我需要添加其他列名进行分组，但那不是我想要的方式，而且由于我的数据有很多项，因此以那种方式结果是出乎意料的。

示例数据查询：

从OrderDetails中选择ProductID、ProductName、OrderQuantity

以下为结果：

 ProductID     ProductName    OrderQuantity
    1001          abc               5
    1002          abc               23    (ProductNames can be same)
    2002          xyz               8
    3004          ytp               15
    4001          aze               19
    1001          abc               7     (2nd row of same ProductID)

期望结果：

 ProductID     ProductName    OrderQuantity
    1001          abc               12    (group by productID while summing)
    1002          abc               23
    2002          xyz               8
    3004          ytp               15
    4001          aze               19

如何选择多列并按ProductID列分组，因为ProductName不唯一？

在此过程中，还要获取OrderQuantity列的总和。

- Ozan Ayten

---

你可能想要查看字符串的聚合。不幸的是，我对此没有经验。 - Joe_DM

有很多方法可以做到这一点，但我认为最简单/最好的方法是使用CTE（公共表达式）。 （在您喜欢的搜索引擎上查找） - BenKoshy

Answer 1

当我需要在多列选择中按一列分组时，我使用这个技巧：

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

这有效。

- Urs Marian

---

巧妙，谢谢！对于路过的人：你需要在每个未分组的列周围加上max()，使用as ___将其重命名为你想要显示的内容，并且对不包含max()的要去重的列使用group by。 - Andrew

哈哈，愉快的方法欺骗 SQL，但我想知道这在所有情况下都有效吗？ - ngShravil.py

这似乎没有意义，而且可能是错误的！如果您的数据中每个column_A有多个column_B，那么如果您按Column_A分组并在选择中使用MAX（Column_B）来绕过group-by限制，那么它只是那些column_B值中的一个（这里是由MAX返回的最大值）。这通常不是您想要的！如果您的数据中每个column_A没有不同的column_B值，则应按其他答案所述将您的column_B添加到GROUP BY子句中。 - S.Serpooshan

我同意@Andrew和S.Serpooshan的看法，当我们在列中有不同的值时，它就无法正常工作。 - Appesh

如果您的列类型为布尔值，则此方法不起作用。 - Hermawan Wiwid

Answer 2

我只想添加一种更有效且通用的解决此类问题的方法。 主要思路是使用子查询。

按照表格ID进行分组并加入相同表格的连接。

由于您的productId不是唯一的，因此您的情况更具体，有两种解决方法。

我将从更具体的解决方案开始： 由于您的productId不是唯一的，所以我们需要额外的步骤，在分组和子查询后选择DISTINCT产品ID，如下所示：

WITH CTE_TEST AS (SELECT productId, SUM(OrderQuantity) Total
                    FROM OrderDetails
                    GROUP BY productId)
SELECT DISTINCT(OrderDetails.ProductID), OrderDetails.ProductName, CTE_TEST.Total
FROM OrderDetails 
INNER JOIN CTE_TEST ON CTE_TEST.ProductID = OrderDetails.ProductID

这会精准返回预期内容。

 ProductID     ProductName         Total
    1001          abc               12    
    1002          abc               23
    2002          xyz               8
    3004          ytp               15
    4001          aze               19

但是有一种更加简洁的方法。我猜ProductId是产品表的外键，并且我认为此表中应该有一个OrderId主键(唯一)。

在这种情况下，有几个步骤可以在仅分组一个列的同时包含额外的列。这将与以下解决方案相同

以此t_Value表为例:

如果我想按描述分组并显示所有列。

我要做的只是:

1. 创建WITH CTE_Name子查询，包括您的GroupBy列和COUNT条件
2. 选择值表中的所有内容(或任何您想要显示的内容)和CTE中的总计数
3. INNER JOIN使用ID(主键或唯一约束)列

就是这样!

以下是查询：

WITH CTE_TEST AS (SELECT Description, MAX(Id) specID, COUNT(Description) quantity 
                    FROM sch_dta.t_value
                    GROUP BY Description)
SELECT sch_dta.t_Value.*, CTE_TEST.quantity 
FROM sch_dta.t_Value 
INNER JOIN CTE_TEST ON CTE_TEST.specID = sch_dta.t_Value.Id

这是结果：

- Haithem KAROUI

Answer 3

您的数据

DECLARE @OrderDetails TABLE 
(ProductID INT,ProductName VARCHAR(10), OrderQuantity INT)

INSERT INTO @OrderDetails VALUES
(1001,'abc',5),(1002,'abc',23),(2002,'xyz',8),
(3004,'ytp',15),(4001,'aze',19),(1001,'abc',7)

查询

 Select ProductID, ProductName, Sum(OrderQuantity) AS Total
 from @OrderDetails 
 Group By ProductID, ProductName  ORDER BY ProductID

结果

╔═══════════╦═════════════╦═══════╗
║ ProductID ║ ProductName ║ Total ║
╠═══════════╬═════════════╬═══════╣
║      1001 ║ abc         ║    12 ║
║      1002 ║ abc         ║    23 ║
║      2002 ║ xyz         ║     8 ║
║      3004 ║ ytp         ║    15 ║
║      4001 ║ aze         ║    19 ║
╚═══════════╩═════════════╩═══════╝

- M.Ali

---

但我说过，我不想添加其他列名来分组，因为这会导致意料之外的结果。 - Ozan Ayten

除非您有多个与同一ProductID相关联的Productnames，否则它不应该给您带来意外的结果。如果是这种情况，并且您想避免这种情况，请查看我的更新。 - M.Ali

是的，有相同的产品名称，我应该在之前加上抱歉。编辑过的查询有效，但存在一个小问题，被求和的行是重复的。 - Ozan Ayten

@OzanAyten 我已经使用了你更新后的数据和相同的查询，结果显示与你期望的结果集一致。 - M.Ali

是的，但我的问题已经尽可能清晰明了。如果我把所有数据都放在问题里，那么很多内容都无法理解。这就是为什么我要问如何在只分组一列的情况下选择多列的原因。 - Ozan Ayten

Answer 4

MySQL 的 GROUP_CONCAT 函数可以帮助实现分组后的字符串拼接，具体用法请参考官方文档

SELECT ProductID, GROUP_CONCAT(DISTINCT ProductName) as Names, SUM(OrderQuantity)
FROM OrderDetails GROUP BY ProductID

这将返回以下内容：

ProductID     Names          OrderQuantity
1001          red            5
1002          red,black      6
1003          orange         8
1004          black,orange   15

这个与 @Urs Marian 在这里发帖的想法类似 https://dev59.com/IWEi5IYBdhLWcg3wfsfI#38779277

- Ivar

---

非常酷的功能 :) 看起来微软终于在一段时间内得到了类似的东西，https://database.guide/the-sql-server-equivalent-to-group_concat/ - Jessica Pennell

Answer 5

您可以尝试以下查询。我假设您的所有数据都在单个表中。

SELECT OD.ProductID, OD.ProductName, CalQ.OrderQuantity
FROM (SELECT DISTINCT ProductID, ProductName
      FROM OrderDetails) OD
INNER JOIN (SELECT ProductID, OrderQuantity SUM(OrderQuantity)
            FROM OrderDetails
            GROUP BY ProductID) CalQ
ON CalQ.ProductID = OD.ProductID

- A_B

Answer 6

你可以尝试这个：

Select ProductID,ProductName,Sum(OrderQuantity) 
 from OrderDetails Group By ProductID, ProductName

你只需要对不带聚合函数的列进行Group By，因此在这种情况下，你可以只使用Group By ProductID和ProductName。

- har07

---

同样在这个回答中，我说过，我不想添加其他列名到分组依据中，因为它会产生意外的结果。 - Ozan Ayten

一个产品ID只能有一个相关的产品名称，对吧？因此，在这种情况下按ProductId、ProductName分组将得到与按ProductId分组相同的结果。 - har07

ProductName不是唯一的，只有ProductID是唯一的。此外，我知道你在回答中的意思，但在我的问题中，我只是要求按照一个列进行分组。 - Ozan Ayten

Answer 7

在我看来，这是一个严重的语言缺陷，使SQL远远落后于其他语言。这是我的极其hacky的解决方法。它是一个完全的补救措施，但它总是有效的。

在我开始之前，我想引起@Peter Mortensen的注意，他的回答在我看来是正确的。我之所以做以下操作，是因为大多数SQL实现具有非常慢的连接操作，并强制你打破"不要重复自己"的原则。我需要我的查询快速填充。

此外，这是一种旧的做事方式。STRING_AGG和STRING_SPLIT更加简洁。再次之所以我这样做是因为它总是有效的。

-- remember Substring is 1 indexed, not 0 indexed
SELECT ProductId
  , SUBSTRING (
      MAX(enc.pnameANDoq), 1, CHARINDEX(';', MAX(enc.pnameANDoq)) - 1
    ) AS ProductName
  , SUM ( CAST ( SUBSTRING (
      MAX(enc.pnameAndoq), CHARINDEX(';', MAX(enc.pnameANDoq)) + 1, 9999
    ) AS INT ) ) AS OrderQuantity
FROM (
    SELECT CONCAT (ProductName, ';', CAST(OrderQuantity AS VARCHAR(10)))
      AS pnameANDoq, ProductID
    FROM OrderDetails
  ) enc
GROUP BY ProductId

或者用简单的语言来说：

• 将除一个字段之外的所有内容粘合在一起，使用一个你知道不会被使用的分隔符
• 使用子字符串提取分组后的数据

从性能上来看，我一直使用字符串比使用像 bigints 这样的东西要好。至少在微软和 Oracle 中，子字符串是一个快速操作。

这避免了当你使用 MAX() 时遇到的问题，其中当你在多个字段上使用 MAX() 时，它们不再一致并来自不同的行。在这种情况下，你的数据保证被粘合在一起，完全按照你要求的方式。

要访问第三个或第四个字段，你需要嵌套子字符串，“在第一个分号后查找第二个”。如果 STRING_SPLIT 可用，则更好。

注意：虽然超出了你的问题范围，但在相反的情况下，当你在组合键上进行分组时，这尤其有用，但不想显示每种可能的排列，也就是说，你想将“foo”和“bar”公开为一个组合键，但想按“foo”分组。

- Jessica Pennell

Answer 8

    WITH CTE_SUM AS (
      SELECT ProductID, Sum(OrderQuantity) AS TotalOrderQuantity 
      FROM OrderDetails GROUP BY ProductID
    )
    SELECT DISTINCT OrderDetails.ProductID, OrderDetails.ProductName, OrderDetails.OrderQuantity,CTE_SUM.TotalOrderQuantity 
    FROM 
    OrderDetails INNER JOIN CTE_SUM 
    ON OrderDetails.ProductID = CTE_SUM.ProductID

请检查是否有效。

- Vikram

Answer 9

==编辑==

我再次检查了您的问题，并得出结论，这是做不到的。

ProductName 不唯一，它必须成为 Group By 的一部分或从结果中排除。

例如，如果您只按 ProductID 进行 Group By，SQL 会如何向您呈现这些结果？

ProductID | ProductName | OrderQuantity 
---------------------------------------
1234      | abc         | 1
1234      | def         | 1
1234      | ghi         | 1
1234      | jkl         | 1

- Joe_DM

---

我已经附上了我能想到的不需要按两个项目分组的唯一方法。问题在于，如果您按数字分组，则无法选择相应的字符串而不进行一些聚合。@har07发布的答案似乎是最佳选择。例如，如果两个项目具有相同的OrderQuantity但具有不同的ProductName，则服务器不知道要向您显示哪个ProductName。希望这讲得通。 - Joe_DM

我想合并并求和相同ProductID的行的OrderQuantity :) 我也知道为什么这不起作用。这一切都很有道理，但真的不可能吗？ - Ozan Ayten

我刚注意到这会让你回到原点... 你得到的结果有什么问题吗？也许你的查询正是你想要的，只是格式错误了？ - Joe_DM

抱歉，但正如我在问题中所述，我还需要其他列。我知道如何进行分组。我可以自己做到这一点，但我的问题不同。我已经编辑了我的问题，请阅读最后一句话。 - Ozan Ayten

如果问题是您想要显示OrderID，那么这是不可能的，因为多个订单将具有不同的OrderID，但将按ProductID分组。您能否进一步说明您想要实现什么，而您发布的查询无法实现？ - Joe_DM

Answer 10

我遇到了与OP类似的问题。然后我看到了@Urs Marian的答案，这对我帮助很大。 但是我还想知道的是，当一列中有多个值并且它们将被分组时，如何获取最后提交的值（例如按日期/ID列排序）。

例如：

我们有以下表结构：

CREATE TABLE tablename(
    [msgid] [int] NOT NULL,
    [userid] [int] NOT NULL,
    [username] [varchar](70) NOT NULL,
    [message] [varchar](5000) NOT NULL
) 

现在表格中至少有两个数据集：

+-------+--------+----------+---------+
| msgid | userid | username | message |
+-------+--------+----------+---------+
|     1 |      1 | userA    | hello   |
|     2 |      1 | userB    | world   |
+-------+--------+----------+---------+

因此，以下SQL脚本可以工作（在MSSQL上进行了检查）以对其进行分组，即使相同的userid具有不同的username值。在下面的示例中，将显示msgid最高的用户名：

SELECT m.userid, 
(select top 1 username from table where userid = m.userid order by msgid desc) as username,
count(*) as messages
FROM tablename m
GROUP BY m.userid
ORDER BY count(*) DESC

- DubZ