如何获取每个供应商最高采购总额，仅保留单条最高值记录

以下是满足需求的优化查询，能确保每个供应商仅保留采购总额最高的一条记录，并按供应商名称排序：

WITH SupplierTotal AS (
    SELECT 
        s.sup_nama,
        SUM(pd.pengd_jumlah * pd.pengd_harga) AS Total_Pengadaan
    FROM [Training].[dbo].[Pengadaan_Header] ph
    JOIN [Training].[dbo].[Supplier] s ON ph.supplier_id = s.sup_id
    JOIN [Training].[dbo].[Pengadaan_Detail] pd ON ph.peng_id = pd.peng_id
    GROUP BY s.sup_nama
),
RankedSuppliers AS (
    SELECT 
        sup_nama,
        Total_Pengadaan,
        ROW_NUMBER() OVER (PARTITION BY sup_nama ORDER BY Total_Pengadaan DESC) AS rn
    FROM SupplierTotal
)
SELECT sup_nama, Total_Pengadaan
FROM RankedSuppliers
WHERE rn = 1
ORDER BY sup_nama ASC;

关键说明： #

1. CTE SupplierTotal：先计算每个供应商的累计采购总额，通过关联采购表头、供应商和采购明细表，按供应商名称分组求和，得到每个供应商的总采购额。
2. CTE RankedSuppliers：使用ROW_NUMBER()窗口函数，按供应商名称分组（PARTITION BY sup_nama），对总采购额降序排序，给每个供应商的记录标记排名，最高总额的记录排名为1。
3. 最终筛选：只保留排名为1的记录，确保每个供应商仅一条最高总额数据，最后按供应商名称升序排序。

原查询的问题： #

• 原查询的子查询未起到筛选最大值的作用，反而造成无效关联；
• 分组字段包含了单条明细的金额（pengd_jumlah * pengd_harga），导致每个供应商会按明细金额分组，出现多条记录。

内容的提问来源于stack exchange，提问作者Rayhan