按纵列相加作分母,对应的值作分子
我知道怎么算的,但不会SQL语句! :(
我知道怎么算的,但不会SQL语句! :(
解决方案 »
- 100分求解连接MSSQL2000时出现的问题
- 排序问题求救(急)
- 请问怎样将DB格式的表内容及结构转化为SQL Server的数据库表?
- 一条Select语句,百思不得其解(再加100分请教)
- 急,一个很简单的问题,但是我就是不明白怎么做?!!
- sql问题
- “很喜欢 恶心”这样的sql模糊查询有人实现了吗??
- 急在win98下安装的sql7后,更改计算机名后无法连接数据库,怎么办?
- 在線等待,急阿!觸發器中的變量申明附值,多謝幫忙!
- 关于数据库的导入
- 我的MSSQL2000数据库自从我的系统从XPHOME升级到XPP版以后表都打不开了,
- 用insert into select from 语句 如何不写入日志?
convert(varchar(10),参数1/参2)+'%' as 接收比例 from tb
cast(100.0*本期接收数/(select sum(本期接收数) from tbl) as varchar(10))+'%' as 接收比例,
cast(100.0*本期发放数/(select sum(本期发放数) from tbl) as varchar(10))+'%' as 发放比例,
cast(100.0*本期库存数/(select sum(本期库存数) from tbl) as varchar(10))+'%' as 库存比例
from tbl
select *,
结存比例=上期结存数/( select sum(上期结存数) from tablename )
接收比例=本期接收数/( select sum(本期接收数) from tablename )
发放比例=本期发放数/( select sum(本期发放数) from tablename )
库存比例=库存数/( select sum(库存数) from tablename )
from tablename
本期接收数 /(select sum(本期接收数) from 表) 接收比例,
本期发放数 /(select sum(本期发放数) from 表) 发放比例,
库存数 /(select sum(库存数) from 表) 库存比例
from 表
cast(本期接收数/(0.01*(Select sum(本期接收数) from 表名)) as nvarchar)+'%' as 接收比例,
cast(本期发放数/(0.01*(Select sum(本期发放数) from 表名)) as nvarchar)+'%' as 发放比例,
cast(库存数/(0.01*(Select sum(库存数) from 表名)) as nvarchar)+'%' as 库存比例,
from 表名
接收比例=left(cast(本期接收数 as numeric(8,4))/(select sum(本期接收数) from 表名)*100,5 )+'%' ,
发放比例=left(cast(本期发放数 as numeric(8,4))/(select sum(本期接收数) from 表名)*100,5 )+'%',
库存比例=left(cast(库存数 as numeric(8,4))/(select sum(本期接收数) from 表名)*100,5)+'%'
from 表名;--测试结果:
单鞋 1000 1500 800 1700 55.55% 29.62% 62.96%
棉鞋 500 300 600 200 11.11% 22.22% 7.407%
凉鞋 100 900 200 800 33.33% 7.407% 29.62%
接收比例=left(cast(本期接收数 as numeric(8,4))/(select sum(本期接收数) from tb)*100,5 )+'%' ,
发放比例=left(cast(本期发放数 as numeric(8,4))/(select sum(本期发放数) from tb)*100,5 )+'%',
库存比例=left(cast(库存数 as numeric(8,4))/(select sum(本期接收数) from tb)*100,5)+'%'
from tb;
--改成这样可以得到楼主的结果:
本期接收数,
本期发放数,
库存数,
计算接收比例 as 接收比例,
计算发放比例 as 发放比例,
计算库存比例 as 库存比例from 表/*计算..比例为你的计算方法*/
接收比例=(select distinct case when sum(本期接收数)=0 then '0.00%'
else left(cast(a.本期接收数 as numeric(8,4))/sum(本期接收数)*100,5 )+'%' end from tb) ,
发放比例=(select distinct case when sum(本期发放数)=0 then '0.00%'
else left(cast(a.本期发放数 as numeric(8,4))/sum(本期发放数)*100,5 )+'%' end from tb),
库存比例=(select distinct case when sum(库存数)=0 then '0.00%'
else left(cast(a.本期接收数 as numeric(8,4))/sum(本期接收数)*100,5 )+'%' end from tb)
from tb a;