如何仅按USC.UseCaseCapabilityId分组，无需聚合函数获取视图字段？

要实现仅按USC.UseCaseCapabilityId分组、保留所有原字段且不使用传统GROUP BY聚合函数的需求，你可以利用窗口函数替代分组聚合，具体有两种实现方式：

方式1：保留所有原始记录，同时展示分组统计值 #

这种方式会保留所有符合过滤条件的记录，每条记录都会显示其所属UseCaseCapabilityId分组的统计值（供应商数量、文档数等），无需使用GROUP BY子句：

CREATE VIEW analysis.vwOnboardingTableData
AS
SELECT 
       USC.UseCaseCapabilityId,
       USC.Name,
       USC.JobId,
       USC.Id AS UseCaseId,
       -- 窗口函数计算该CapabilityId下的供应商总数
       COUNT(JSOR.SupplierKey) OVER (PARTITION BY USC.UseCaseCapabilityId) AS SupplierCount,
       JSOR.OnboardingRouteId,
       -- 窗口函数计算该CapabilityId下的各类统计值
       SUM(JS.SupplierPaymentDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS PaymentsCount,
       SUM(JS.SupplierInvoiceDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS InvoicesCount,
       SUM(JS.SupplierPurchaseOrderDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS PoCount,
       SUM(JS.SupplierSpendValueCurrencyJob) OVER (PARTITION BY USC.UseCaseCapabilityId) AS Spend,
       JM.JobSpendSource
FROM analysis.UseCases AS USC
INNER JOIN analysis.UseCaseSupplierStates AS JSOR
    ON USC.Id = JSOR.UseCaseId
    AND USC.JobId = JSOR.JobId
INNER JOIN analysis.vwJobSupplierWithScores AS JS
    ON JSOR.SupplierKey = JS.SupplierKey
INNER JOIN analysis.vwJobMetrics AS JM
    ON USC.JobId = JM.JobId
WHERE (USC.IsTemplate = 0)
  AND (JSOR.IsQualified = 1)
GO

如果需要对相同UseCaseCapabilityId的记录去重，可在SELECT后添加DISTINCT关键字。

方式2：每个UseCaseCapabilityId仅返回一行记录 #

如果要求每个UseCaseCapabilityId只输出一行结果，可结合ROW_NUMBER()窗口函数筛选每组内的任意一条记录（这里按UseCaseId排序取第一条）：

CREATE VIEW analysis.vwOnboardingTableData
AS
SELECT 
       UseCaseCapabilityId,
       Name,
       JobId,
       UseCaseId,
       SupplierCount,
       OnboardingRouteId,
       PaymentsCount,
       InvoicesCount,
       PoCount,
       Spend,
       JobSpendSource
FROM (
    SELECT 
           USC.UseCaseCapabilityId,
           USC.Name,
           USC.JobId,
           USC.Id AS UseCaseId,
           COUNT(JSOR.SupplierKey) OVER (PARTITION BY USC.UseCaseCapabilityId) AS SupplierCount,
           JSOR.OnboardingRouteId,
           SUM(JS.SupplierPaymentDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS PaymentsCount,
           SUM(JS.SupplierInvoiceDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS InvoicesCount,
           SUM(JS.SupplierPurchaseOrderDocCount) OVER (PARTITION BY USC.UseCaseCapabilityId) AS PoCount,
           SUM(JS.SupplierSpendValueCurrencyJob) OVER (PARTITION BY USC.UseCaseCapabilityId) AS Spend,
           JM.JobSpendSource,
           -- 给每个CapabilityId分组内的记录编号
           ROW_NUMBER() OVER (PARTITION BY USC.UseCaseCapabilityId ORDER BY USC.Id) AS rn
    FROM analysis.UseCases AS USC
    INNER JOIN analysis.UseCaseSupplierStates AS JSOR
        ON USC.Id = JSOR.UseCaseId
        AND USC.JobId = JSOR.JobId
    INNER JOIN analysis.vwJobSupplierWithScores AS JS
        ON JSOR.SupplierKey = JS.SupplierKey
    INNER JOIN analysis.vwJobMetrics AS JM
        ON USC.JobId = JM.JobId
    WHERE (USC.IsTemplate = 0)
      AND (JSOR.IsQualified = 1)
) AS subquery
-- 仅取每组的第一条记录
WHERE rn = 1
GO

关键说明 #

• 窗口函数OVER (PARTITION BY USC.UseCaseCapabilityId)会将数据按指定字段分组计算统计值，但不会改变原始的行结构，完美满足保留所有原字段的需求。
• 这里使用的COUNT/SUM属于窗口聚合，而非传统GROUP BY的分组聚合，符合你“不使用SUM、COUNT等聚合函数配合GROUP BY”的要求。

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