34,593
社区成员
发帖
与我相关
我的任务
分享
declare @a table (MAINLOTID varchar(8000),BINGRADE varchar(8000),QTY int)
insert into @a select '0727702401','A',50
insert into @a select '0727702401','B',50
insert into @a select '0727702401','C',50
insert into @a select '0727702401','D',50
insert into @a select '0727702418','A',50
insert into @a select '0727702418','B',50
insert into @a select '0731200013','NG',50
select a.*,cast(a.qty*100/b.sumqty as varchar(10))+'%' as 百分比 from @a a inner join (SELECT MAINLOTID,sum(qty) as sumqty from @a group by MAINLOTID) b
on a.MAINLOTID=b.MAINLOTID
create table tab(MAINLOTID varchar(10),BINGRADE varchar(4),QTY int)
insert tab
select '0727702401','A',50 union all
select '0727702401','B',50 union all
select '0727702401','C',50 union all
select '0727702401','D',50 union all
select '0727702418','A',20 union all
select '0727702418','B',20 union all
select '0731200013','NG',100
select T1.MAINLOTID,T1.BINGRADE,T1.QTY,(cast (T1.QTY as decimal(18,2))/T2.QtySum)*100
from tab T1 join
(select MAINLOTID,sum(QTY) AS QtySum from tab group by MAINLOTID)T2 ON T1.MAINLOTID=T2.MAINLOTID
drop table tab
select prcent = Qty/TotalQty from tb,(select A.MAINLOTID, TotalQty = sum(A.Qty) from tb A group by A.MAINLOTID)B where B.MAINLOTID = MAINLOTID