補充一下:
我有時候得到的結果是這個:Liability PercentByLiability
--------- ----------------------
Compal 0.566679076642467
Customer 0.428410985810709有時候是這個Liability PercentByLiability
--------- ----------------------
Customer 0.428410985810709
Compal 0.566679076642467
Vendor 0.00490993754682345結果對都是對的,就是它的行數總是在變...有沒有好的寫法可以解決,union all肯定是不能少的.
我有時候得到的結果是這個:Liability PercentByLiability
--------- ----------------------
Compal 0.566679076642467
Customer 0.428410985810709有時候是這個Liability PercentByLiability
--------- ----------------------
Customer 0.428410985810709
Compal 0.566679076642467
Vendor 0.00490993754682345結果對都是對的,就是它的行數總是在變...有沒有好的寫法可以解決,union all肯定是不能少的.
INSERT TB
SELECT 'Customer', -1420143 UNION ALL
SELECT 'Compal', -1878488.99910294 UNION ALL
SELECT 'Vendor', -16275.9912058823SELECT [Liability],[Amount]/SUM([Amount]) OVER () AS PercentByLiability
FROM TB DROP TABLE TB
/*
Liability PercentByLiability
--------- ----------------------
Customer 0.428410985810709
Compal 0.566679076642467
Vendor 0.00490993754682343
*/
Liability
,Amount/(select sum(Amount) from ScrapTest ) as PercentByLiability
from ScrapTest
select Liability,sum(Amount)Amount from(
select 'Vendor' as Liability,Amount from ScrapTest where Reas=12 and substring(mvt,1,2)='55' --廠商責任
union all
select 'Customer' as Liability,Amount from ScrapTest where Sloc='T001' and substring(mvt,1,2)='45' --客戶責任
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=2 and substring(mvt,1,2)='Z2' --NPI拆機
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=3 and substring(mvt,1,2)='55' --Purge文報廢
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=6 and substring(mvt,1,2)='55' and (substring(Sloc,1,2)='QR' or substring(Sloc,1,2)='RT' or substring(Sloc,1,2)='TW') --RMA維修報廢
union all
select 'Compal' as Liability,Amount from ScrapTest where (Reas=1 or Reas=12) and substring(mvt,1,2)='55' --Test/Turn parts scrap
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=12 and Sloc='SQA1' and mvt='555' --GP測試報廢
union all
select 'Compal' as Liability,Amount from ScrapTest where (Reas=1 or Reas=2) and substring(mvt,1,2)='Z2' and substring(Sloc,1,1)='M' --MP制損
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=5 and substring(mvt,1,2)='55' and substring(Sloc,1,1)='P' --PE repair
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=4 and substring(mvt,1,2)='55' --Free samle報廢
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=15 and substring(mvt,1,2)='Z2' and substring(Sloc,1,2)='M0'--SMT拋料
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=21 and substring(mvt,1,2)='55' and (substring(Sloc,1,1)='T' and Sloc<>'Tfru' and Sloc<>'TFR1') --MME核示報廢
union all
select 'Compal' as Liability,Amount from ScrapTest where Reas=21 and substring(mvt,1,2)='55' and (Sloc='Tfru' or Sloc='TFR1') --Service scrap
) A group by Liability
)
select
Liability
,Amount/(select sum(Amount) from ScrapByLiability ) as PercentByLiability
from ScrapByLiability
insert @t
select 'customer', -1420143 union all
select 'compal', -1878488.99910294 union all
select 'vendor', -16275.9912058823--这样数据多的时候可能略微高效点
declare @sum int
select @sum=sum(amount) from @t select liability,amount/@sum as percentbyliability
from @t Liability PercentByLiability
--------- ----------------------
Customer 0.428411113795953
Compal 0.566679245934483
Vendor 0.00490993901363818(3 行受影响)