34,587
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t AS TABLE(物料 NVARCHAR(50) NOT NULL, 供应商 NVARCHAR(50) NOT NULL, 数量 DECIMAL(18,6) NOT NULL, 金额 DECIMAL(18,2) NOT NULL)
INSERT @t(物料, 供应商, 数量, 金额)
VALUES(N'A','X',100, 1000),
('A','X',50,500),
('A','Y',200,1500),
('B','Y',200,200),
('B','Z',100,100)
;WITH g AS(
SELECT 物料,供应商,SUM(数量) 总数量,SUM(金额) 总金额 FROM @t GROUP BY 物料, 供应商)
SELECT *, g.总金额/ SUM(g.总金额) OVER (PARTITION BY g.物料) AS 金额比例 FROM g
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([物料] nvarchar(21),[供应商] nvarchar(21),[数量] int,[金额] int)
Insert #A
select N'A',N'X',100,1000 union all
select N'A',N'X',50,500 union all
select N'A',N'Y',200,1500 union all
select N'B',N'Y',200,200 union all
select N'B',N'Z',100,100
Go
--测试数据结束
;WITH cte AS (
Select 物料,供应商,SUM(数量)数量,SUM(金额)金额 from #A GROUP BY 物料,供应商
)
SELECT *,
CONVERT(FLOAT, cte.金额) / SUM(cte.金额) OVER (PARTITION BY cte.物料) AS 金额占比
FROM cte;
with a as
(select [物料],[供应商],SUM([金额]) as j1 from #a group by [物料],[供应商]),
b as
(select [物料],SUM([金额]) as j2 from #a group by [物料])
select a.[物料],a.[供应商],j1,j2,j1*1.0/j2 as j3 from a, b where a.物料=b.物料;
create table 表A
(物料 varchar(10),供应商 varchar(10),数量 int,金额 int)
insert into 表A(物料,供应商,数量,金额)
select 'A','X',100,1000 union all
select 'A','X',50,500 union all
select 'A','Y',200,1500 union all
select 'B','Y',200,200 union all
select 'B','Z',100,100
select 物料,
供应商,
总数量=sum(数量),
总金额=sum(金额),
金额占比=sum(金额)*1.0/(select sum(b.金额) from 表A b where b.物料=a.物料)
from 表A a
group by 物料,供应商
/*
物料 供应商 总数量 总金额 金额占比
---------- ---------- ----------- ----------- ---------------------------------------
A X 150 1500 0.500000000000
A Y 200 1500 0.500000000000
B Y 200 200 0.666666666666
B Z 100 100 0.333333333333
(4 row(s) affected)
*/