在 GROUP BY 分组中，根据另一列的最高值选择值。

Question

我试图构建一个查询，将GROUP BY组缩减为一行，并包括基于另一列的最大值的列的值。在这种情况下，我希望获得一个项目ID、订单总数量和最常用的供应商。

我已经成功地构建了一个查询，将订购数量求和并按项目和供应商分组，得出：

| id | qty | supplier       |
|  1 | 20  | S&S Activewear |
|  1 | 10  | J&J Textiles   |
|  2 | 5   | AB Footwear    |
|  2 | 10  | CD Shoes       |

预期的结果应该是所有供应商的订单总量以及最常用的供应商，因此:

| id | total_qty | most_used_supplier |
|  1 | 30        | S&S Activewear     |
|  2 | 15        | CD Shoes           |

从概念上讲，我想进行一个子查询，仅按id分组以上结果，然后汇总(qty)，并通过qty在GROUP BY中的排名来选择供应商的值。

我已经阅读了很多相关帖子，但是我未能成功地将这些方法应用到这个问题上，包括使用ROW_NUMBER和PARTITION_BY。

我正在使用Elixir和Ecto在Postgres数据库中执行此操作，但为了使其通用，让任何人都可以回答，我只是想了解如何在SQL中完成此操作。 如果需要更多细节，请告诉我，谢谢。

- harry_sundown

---

会有平局吗？ - shawnt00

或者，如果两个供应商提供相同的total_Quantity，我们应该如何处理平局？按字母顺序？ - xQbert

领带极不可能。我想，如果这种情况下能够将两个供应商连接起来，那将是一个理想的解决方案，但对于我的目的来说，只显示一个也可以。 - harry_sundown

Answer 1

有几种方法可以实现，听起来你已经尝试过其中一种：

with data as (
    select *,
        row_number() over (partition by id order by qty desc) as rn
    from T
)
select id, sum(qty) as total_qty,
    (select d2.supplier from data d2
        where d2.id = d.id and rn = 1) as most_used_supplier
from data d
group by id;

- shawnt00

---

感谢@shawnt00，我所需的复杂度越来越高，因此问题也发生了变化，但我确实在最终解决方案中利用了这个解决方案，因此将其标记为正确答案。 - harry_sundown

Answer 2

我把问题分成两部分。首先，找到最大数量，然后将数量相加。最后，连接表格以获得答案。

SELECT T4.ID, T5.sumQty AS total_qty,T4.supplier AS most_used_supplier
FROM [Test].[dbo].[Test] AS T4 LEFT JOIN
(
    SELECT ID,SUM(QTY) as sumQty
    FROM [Test].[dbo].[Test]
    GROUP BY ID
)AS T5
ON T4.ID = T5.ID
WHERE supplier IN
(
    SELECT supplier 
    FROM [Test].[dbo].[Test] AS T1 LEFT JOIN
        (
          SELECT MAX(qty) AS maxQty, ID
          FROM [Test].[dbo].[Test] AS T
          GROUP BY id
        ) AS T2
    ON T1.ID = T2.ID
    AND T1.qty = T2.maxQty
    WHERE T2.ID IS NOT NULL
)

- Gabriel Bonaventura

Answer 3

我建议使用多个子查询：

select id, sum(qty),
       (select t2.supplier
        from t t2
        where t2.id = t.id
        order by t2.qty desc
        fetch first 1 row only
       ) as supplier
from t
group by id;

这个使用标准的语法来返回一行数据。你的数据库可能有另一种语法来表示相当于fetch first 1 row only的功能。

- Gordon Linoff

Answer 4

首先找到每个ID的最大数量。 然后找到提供这些最大数量的适当供应商。如果有多个“最大”的情况，可能会出现问题，您需要看看如何处理它。 最后，将其再次连接到同一张表中，添加适当的数量总和。

SELECT item.id, sum(item.qty) total_qty, biggestSupplier.supplier most_used_supplier
from item join
(
    SELECT item.id, supplier       
    from item
    JOIN 
    (
        SELECT id, max(qty) maxqty
        FROM item
        GROUP BY id
    ) maxQtyForId ON item.id = maxQtyForId.id AND item.qty = maxQtyForId.maxqty
) biggestSupplier ON item.id = biggestSupplier.id
group by item.id, biggestSupplier.supplier       

- Dragan Jovanović