SQL多列查询只按一列条件求和

img

SQL没有列入GROUP BY 的字段无法查询

？？请问如何在MSSQL中想要作如图示的求和？？——>>只按材料品号分组+考虑顺序=类似于EXCEL动态区域的sumif

（附建表语句：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AT996]') AND type in (N'U'))
DROP TABLE [dbo].[AT996]
GO

CREATE TABLE [dbo].[AT996](
    [单别] [varchar](10) NULL,
    [单号] [varchar](10) NULL,
    [产品品号] [varchar](10) NULL,
    [材料品号] [varchar](10) NULL,
    [需领用量] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[AT996]
([单别],[单号],[产品品号],[材料品号],[需领用量])     VALUES
 ('DB1',    'DH1',    'CP1',    'CL1',    13)
,('DB1',    'DH1',    'CP1',    'CL2',    24)
,('DB1',    'DH1',    'CP1',    'CL3',    35)
,('DB1',    'DH2',    'CP2',    'CL1',    46)
,('DB1',    'DH3',    'CP3',    'CL1',    57)
GO

参考下这个
SELECT
a.code 工号,
a.yearmonth 年月,
a.bouns 奖金,
SUM(a.bouns) OVER (PARTITION BY a.code ORDER BY a.yearmonth ) AS 本年累计
FROM
bouns a
ORDER BY
a.yearmonth ,a.code

img



with t ([单别],[单号],[产品品号],[材料品号],[需领用量])     as
 (select 'DB1',    'DH1',    'CP1',    'CL1',    13
 union all select 'DB1',    'DH1',    'CP1',    'CL2',    24
 union all select 'DB1',    'DH1',    'CP1',    'CL3',    35
 union all select 'DB1',    'DH2',    'CP2',    'CL1',    46
 union all select 'DB1',    'DH3',    'CP3',    'CL1',    57)
,t1 as (
 select *
    ,row_number() over(partition by 材料品号 order by [产品品号]) nid 
 from t
)
select * from t1 a
cross apply (
    select sum([需领用量]) cnt
    from t1
    where nid<=a.nid and 材料品号=a.材料品号
) b

或者用递归做也可以

img