SQL Group BY SUM 一列并选择分组项目的第一行

Question

我有一个零件表，其中有 5 个字段。我想对 mfgpn 的数量求和，同时显示其他 3 个字段(Manfucturer、DateCode、Description)的第一个返回行。我最初想如下使用 MIN 函数，但就数据不是 int 数据类型而言，这并没有真正帮助我。我该怎么做呢？现在我被困在以下查询中:

SELECT SUM([QTY]) AS QTY
  ,[MFGPN]
  ,MIN([MANUFACTURER]) AS MANUFACTURER
  ,MIN([DATECODE]) AS DateCode
  ,MIN([DESCRIPTION]) AS DESCRIPTION
  INTO part
GROUP BY MFGPN, MANUFACTURER, DATECODE, description
ORDER BY mfgpn ASC

Answer 1

会 CROSS APPLY为你工作？

SELECT 
    SUM(a.[QTY]) AS QTY
    ,a.[MFGPN]
    ,c.[MANUFACTURER]
    ,c.[DATECODE]
    ,c.[DESCRIPTION]
FROM part a
    CROSS APPLY (SELECT TOP 1 * FROM part b WHERE a.[MFGPN] = b.[MFGPN]) c
GROUP BY 
    a.[MFGPN]
    ,c.[MANUFACTURER]
    ,c.[DATECODE]
    ,c.[DESCRIPTION]

测试如下:

DECLARE @T1 AS TABLE (
    [QTY] int
    ,[MFGPN] NVARCHAR(50)
    ,[MANUFACTURER] NVARCHAR(50)
    ,[DATECODE] DATE
    ,[DESCRIPTION] NVARCHAR(50));

INSERT @T1 VALUES
(2, 'MFGPN-1', 'MANUFACTURER-A', '20120101', 'A-1'),
(4, 'MFGPN-1', 'MANUFACTURER-B', '20120102', 'B-1'),
(3, 'MFGPN-1', 'MANUFACTURER-C', '20120103', 'C-1'),
(1, 'MFGPN-2', 'MANUFACTURER-A', '20120101', 'A-2'),
(5, 'MFGPN-2', 'MANUFACTURER-B', '20120101', 'B-2')

SELECT 
    SUM(a.[QTY]) AS QTY
    ,a.[MFGPN]
    ,c.[MANUFACTURER]
    ,c.[DATECODE]
    ,c.[DESCRIPTION]
FROM @T1 a
    CROSS APPLY (SELECT TOP 1 * FROM @T1 b WHERE a.[MFGPN] = b.[MFGPN]) c
GROUP BY 
    a.[MFGPN]
    ,c.[MANUFACTURER]
    ,c.[DATECODE]
    ,c.[DESCRIPTION]

生产

QTY MFGPN   MANUFACTURER    DATECODE    DESCRIPTION
9   MFGPN-1 MANUFACTURER-A  2012-01-01  A-1
6   MFGPN-2 MANUFACTURER-A  2012-01-01  A-2