22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[pinming] nvarchar(24),[guige] nvarchar(21),[shuliang] int,[riqi] Date)
Insert #T
select 1,N'肉馅',N'箱',6,'2018-10-10' union all
select 2,N'荠菜肉馅',N'箱',10,'2018-10-10' union all
select 3,N'小混沌',N'箱',5,'2018-10-10' union all
select 4,N'肉馅',N'箱',10,'2018-10-15' union all
select 5,N'小混沌',N'箱',10,'2018-10-15'
Go
--测试数据结束
SELECT t.ID,t.pinming,t.guige,sumshuliang AS 数量,t.riqi FROM (
Select *,SUM(shuliang)OVER(PARTITION BY pinming) AS sumshuliang,ROW_NUMBER()OVER(PARTITION BY pinming ORDER BY ID) rn from #T)t
WHERE rn=1
ORDER BY t.ID
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[pinming] nvarchar(24),[guige] nvarchar(21),[shuliang] int,[riqi] Date)
Insert #T
select 1,N'肉馅',N'箱',6,'2018-10-10' union all
select 2,N'荠菜肉馅',N'箱',10,'2018-10-10' union all
select 3,N'小混沌',N'箱',5,'2018-10-10' union all
select 4,N'肉馅',N'箱',10,'2018-10-15' union all
select 5,N'小混沌',N'箱',10,'2018-10-15'
Go
--测试数据结束
Select *,SUM(shuliang)OVER(PARTITION BY pinming) AS 求和 from #T ORDER BY pinming