sql求百分比,根据jlh 求money字段占moeny字段的总和

发布网友发布时间：2023-06-23 14:44

我来回答

共3个回答

热心网友时间：2024-12-13 12:34

create table tab

(

id int identity(1,1) primary key,

[money] decimal,

jih int

)

insert into tab([money],jih) values(500,1)

insert into tab([money],jih) values(300,1)

insert into tab([money],jih) values(300,2)

insert into tab([money],jih) values(500,2)

insert into tab([money],jih) values(600,3)

insert into tab([money],jih) values(900,3)

insert into tab([money],jih) values(400,4)

insert into tab([money],jih) values(800,4)

select * from tab

select tab.id,tab.money,tabb.total,tab.money,

substring(

convert(varchar(100), round((tab.money/tabb.total),2)),1,4

)

as [avg],

tab.jih

from tab inner join

(select jih,sum([money]) total from tab group by(jih)) tabb

on tab.jih = tabb.jih

运行上面代码即可得到结果

热心网友时间：2024-12-13 12:34

select a.id,a.money,a.jlh, a.money/b.sumMoney as pct
from tablename a
(select jlh, sum(money) as sumMoney from table name group by jlh) b
where a.jlh=b.jlh

热心网友时间：2024-12-13 12:35

select money1/money2||'%' from
(select id as is,sum(money) as money1,0 as jlh,0 as money2 from talbe group by id
union all
select 0 as id,0 as money1,jlh as jlh,sum(money) as money2 from talbe group by jlh)