create table XS(a varchar(3),SL int,date datetime)
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')declare @a int
select @a=sum(sl) from xs
select a,sum(Sl),str(100*convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)),10,1)+'%',convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)) from xs group by a
order by sum(Sl)
---- ----------- ----------- ------------
103 2 14.3% .143
101 4 28.6% .286
102 8 57.1% .571
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')declare @a int
select @a=sum(sl) from xs
select a,sum(Sl),str(100*convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)),10,1)+'%',convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)) from xs group by a
order by sum(Sl)
---- ----------- ----------- ------------
103 2 14.3% .143
101 4 28.6% .286
102 8 57.1% .571
create table XS(a varchar(3),SL int,date datetime)
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')declare @a int
select @a=sum(sl) from xs
select a,sum(Sl),str(100*convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)),10,1)+'%',convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)) from xs group by a
order by sum(Sl) desc
---- ----------- ----------- ------------
103 2 14.3% .143
101 4 28.6% .286
102 8 57.1% .571
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')declare @a int
select @a=sum(sl) from xs
select m.a as 型号,isnull(sum(Sl),0) as 数量,isnull(str(100*convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)),10,1)+'%','0%') as 比例,isnull(convert(numeric(10,3),sum(SL)/convert(numeric(10,1),@a)),0) as 小数
from (select distinct a from xs) M left join (select * from xs where date between '2005-07-01' and '2005-07-31') N on m.a=n.a group by m.a
order by sum(Sl) desc
select 型号,sum(总数量) 总数量,(cast(小数 as varchar(4)) + '%') 比例,(总数量 / cast((select sum(总数量) from 订单表) as decimal(9,3))) 小数 from 订单表 group by 型号
2、
select 型号,sum(总数量) 总数量,(cast(小数 as varchar(4)) + '%') 比例,(总数量 / cast((select sum(总数量) from 订单表) as decimal(9,3))) 小数 from 订单表 group by 型号 where 订单日期 between '2005-07-01' and '2005-07-31'
select 型号,sum(总数量) 总数量,(cast(小数 as varchar(4)) + '%') 比例,(总数量 / cast((select sum(总数量) from 订单表) as decimal(9,3))) 小数 from 订单表 where 订单日期 between '2005-07-01' and '2005-07-31' group by 型号
--测试数据
create table XS(a varchar(3),SL int,dates datetime)
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')
--查询
--问题1:
declare @a float
select @a=sum(sl) from xs
select a,str(100*sum(SL)/@a, 10, 1)+'%'
,convert(numeric(10,3),sum(SL)/@a)
from xs group by a
order by sum(Sl) desc
go
--问题2:
declare @a float
select @a=sum(sl)
from (select a, sl=(case when dates between '2005-7-1' and '2005-7-31'
then sl else 0 end)
from xs)ttselect a,str(100*sum(SL)/@a, 10, 1)+'%'
,convert(numeric(10,3),sum(SL)/@a)
from (select a, sl=(case when dates between '2005-7-1' and '2005-7-31'
then sl else 0 end)
from xs)tt
group by a
order by sum(Sl) desc
--清除
drop table xs
create table XS(a varchar(3),SL int,dates datetime)
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')
--查询
--问题1:
declare @a float
select @a=sum(sl) from xs
select a,总数量=sum(sl)
,比例=str(100*sum(SL)/@a, 10, 1)+'%'
,小数=convert(numeric(10,3),sum(SL)/@a)
from xs group by a
order by sum(Sl) desc
go
--问题2:
declare @a float
select @a=sum(sl)
from (select a, sl=(case when dates between '2005-7-1' and '2005-7-31'
then sl else 0 end)
from xs)ttselect a,总数量=sum(SL)
,比例=str(100*sum(SL)/@a, 10, 1)+'%'
,小数=convert(numeric(10,3),sum(SL)/@a)
from (select a, sl=(case when dates between '2005-7-1' and '2005-7-31'
then sl else 0 end)
from xs)tt
group by a
order by sum(Sl) desc
--清除
drop table xs
是那个取总数值的时候没有加between '2005-07-01' and '2005-07-31'
如果保留的小数位数要变化只要把现在的numeric(10,3)该成相对应的就可以了.
你自己需要多少位就改成对应的自己测试一下.如果比例要改还要注意那个str()函数最后那个参数也要改.
create table XS(a varchar(3),SL int,date datetime)
insert into xs values('101',2,'2005-7-3')
insert into xs values('101',2,'2005-7-4')
insert into xs values('102',2,'2005-7-2')
insert into xs values('102',6,'2005-8-1')
insert into xs values('103',2,'2005-8-2')declare @a int
select @a=sum(sl) from xs where date between '2005-07-01' and '2005-07-31'
select m.a as 型号,isnull(sum(Sl),0) as 数量,isnull(str(100*convert(numeric(10,4),sum(SL)/convert(numeric(10,1),@a)),10,1)+'%','0%') as 比例,isnull(convert(numeric(10,4),sum(SL)/convert(numeric(10,1),@a)),0) as 小数
from (select distinct a from xs) M left join (select * from xs where date between '2005-07-01' and '2005-07-31') N on m.a=n.a group by m.a
order by sum(Sl) desc