你的意思不明确 我想可以这样啊 上面的是A表下面的是B表, select A1.名称,A1.数量,sum(A2.数量) TOTAL from A A1,A A2 group by A1.名称,A1.数量 having A1.数量/TOTAL>0.5
我这样写为什么错误? 表名记为A。select a.名称,a.数量,'比例'=a.数量/sum(a.数量) into #table1 from a order by a.数量 descselect * from #table1上面只是把A表计算为B表。还要取出达到50%的前面N行,就不知道怎么作了?但这个句子尚不正确,为何?
select * from (select a.名称,a.数量,100*a.数量/(select sum(b.数量) from table2 b) as 比例 from table2 a ) c where c.比例>30 order by c.比例 desc
select * from (select a.名称,a.数量,100*a.数量/(select sum(b.数量) from table2 b) as 比例 into #table1 from table2 a ) c where c.比例>30 order by c.比例 desc
declare @intSum intif exists(select * from tempdb..sysobjects where name like '#tmptbale%') drop table #tmptbale select @intSum=sum(数量) from table1 select 名称,数量,convert(char(2),100*数量/@intSum)+'%' as 比例 into #tmptbale from table1 select * from #tmptbale where 比例>'30%' order by 比例 desc
楼主好象要ABC分类法! declare @a int set @a=0 select ' ' 类别,a.名称,a.数量,100*a.数量/(select sum(b.数量) from table2) as 比例 into #temp from 表 order by 比例 desc update #temp set @a=@a+比例,flag=case when @a<30 then 'A' when @a<50 then 'B' else 'C' end select * from #temp drop table #temp
create table #t(名称 char(10),销售额 numeric(11,2))insert into #t values('a',1000) insert into #t values('b',1000) insert into #t values('c',500) insert into #t values('d',800) insert into #t values('e',700) insert into #t values('f',1000) go select 名称, 销售额, 所占比例 = 销售额/(select sum(销售额) from #t) from #t a where (select isnull(sum(销售额),0) from #t b where b.销售额>a.销售额)*2 <= (select sum(销售额) from #t) order by 销售额 desc
我想可以这样啊
上面的是A表下面的是B表,
select A1.名称,A1.数量,sum(A2.数量) TOTAL
from A A1,A A2
group by A1.名称,A1.数量
having A1.数量/TOTAL>0.5
表名记为A。select a.名称,a.数量,'比例'=a.数量/sum(a.数量) into #table1 from a order by a.数量 descselect * from #table1上面只是把A表计算为B表。还要取出达到50%的前面N行,就不知道怎么作了?但这个句子尚不正确,为何?
select @intSum=sum(数量) from table1
select 名称,数量,convert(char(2),100*数量/@intSum)+'%' as 比例 into #tmptbale from table1
select * from #tmptbale where 比例>'30%' order by 比例 desc
declare @a int
set @a=0
select ' ' 类别,a.名称,a.数量,100*a.数量/(select sum(b.数量) from table2) as 比例 into #temp from 表 order by 比例 desc
update #temp set @a=@a+比例,flag=case when @a<30 then 'A' when @a<50 then 'B' else 'C' end
select * from #temp
drop table #temp
insert into #t values('b',1000)
insert into #t values('c',500)
insert into #t values('d',800)
insert into #t values('e',700)
insert into #t values('f',1000)
go
select 名称,
销售额,
所占比例 = 销售额/(select sum(销售额) from #t)
from #t a
where (select isnull(sum(销售额),0) from #t b where b.销售额>a.销售额)*2
<= (select sum(销售额) from #t)
order by 销售额 desc