表格如下:id num sum time
01 3 100 9:01
02 4 100 9:02
03 33 100 9:03
04 12 100 9:00
... .... ...希望得到如下结果id num ratio
01 3 3%
02 7 7%
03 40 40%
04 52 52% 或者
id num ratio
03 33 33%
04 45 45%
02 4 49%
01 3 52%
或者
id num ratio
04 12 12%
01 3 15%
02 4 19%
03 33 52% 目的是分别按照不同的排序顺序计算累加的比例。
01 3 100 9:01
02 4 100 9:02
03 33 100 9:03
04 12 100 9:00
... .... ...希望得到如下结果id num ratio
01 3 3%
02 7 7%
03 40 40%
04 52 52% 或者
id num ratio
03 33 33%
04 45 45%
02 4 49%
01 3 52%
或者
id num ratio
04 12 12%
01 3 15%
02 4 19%
03 33 52% 目的是分别按照不同的排序顺序计算累加的比例。
insert into tb select '01', 3 , 100 , '9:01'
insert into tb select '02' , 4 , 100 , '9:02'
insert into tb select '03' , 33 , 100 , '9:03'
insert into tb select '04' ,12, 100 , '9:00'select id
,num
,cast((total*100/sum) as varchar)+'%' radio
from
(select *,(select sum(num) from tb where id<=a.id) total
from tb a) a
drop table tb/*
id num radio
---- ----------- -------------------------------
01 3 3%
02 4 7%
03 33 40%
04 12 52%(所影响的行数为 4 行)
*/
insert into tb select '01', 3 , 100 , '9:01'
insert into tb select '02' , 4 , 100 , '9:02'
insert into tb select '03' , 33 , 100 , '9:03'
insert into tb select '04' ,12, 100 , '9:00'select id
,num
,cast((total*100/sum) as varchar)+'%' radio
from
(select *,(select sum(num) from tb where num>=a.num) total
from tb a) a
order by num descdrop table tb/*
id num radio
---- ----------- -------------------------------
03 33 33%
04 12 45%
02 4 49%
01 3 52%(所影响的行数为 4 行)
*/
insert into tb select '01', 3 , 100 , '9:01'
insert into tb select '02' , 4 , 100 , '9:02'
insert into tb select '03' , 33 , 100 , '9:03'
insert into tb select '04' ,12, 100 , '9:00'select id
,num
,cast((total*100/sum) as varchar)+'%' radio
from
(select *,(select sum(num) from tb where time<=a.time) total
from tb a) a
order by numdrop table tb/*
id num radio
---- ----------- -------------------------------
01 3 15%
02 4 19%
04 12 12%
03 33 52%(所影响的行数为 4 行)
*/
insert into tb select '01', 3 , 100 , '9:01'
insert into tb select '02' , 4 , 100 , '9:02'
insert into tb select '03' , 33 , 100 , '9:03'
insert into tb select '04' ,12, 100 , '9:00'select id
,num
,cast((total*100/sum) as varchar)+'%' radio
from
(select *,(select sum(num) from tb where time<=a.time) total
from tb a) a
order by totaldrop table tb/*
id num radio
---- ----------- -------------------------------
04 12 12%
01 3 15%
02 4 19%
03 33 52%(所影响的行数为 4 行)
*/
declare @T table (id varchar(2),num int,sum int,time varchar(10))
insert into @T
select '01',3,100,'9:01' union all
select '02',4,100,'9:02' union all
select '03',33,100,'9:03' union all
select '04',12,100,'9:00'if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (seq int identity, id varchar(2),num int,sum int,time varchar(10))
insert #T(id,num,sum,time) select * from @T order by newid()select id, num, ratio = ltrim(cast((select sum(num) from #T where seq <= a.seq) * 100/ sum as numeric(10,0)))+'%' from #T a order by seq
insert into tb select '01', 3 , 100 , '9:01'
insert into tb select '02' , 4 , 100 , '9:02'
insert into tb select '03' , 33 , 100 , '9:03'
insert into tb select '04' ,12, 100 , '9:00'
go
create proc test
@type int
as
declare @sql varchar(8000)
set @sql='select id
,num
,cast((total*100/sum) as varchar)+''%'' radio
from
(select *,(select sum(num) from tb where '
if @type=1
set @sql=@sql+' id<=a.id'
else if @type=2
set @sql=@sql+' num>=a.num'
else
set @sql=@sql+' time<=a.time' set @sql=@sql+') total from tb a) a'
exec (@sql)
goexec test 1
exec test 2
exec test 3drop table tb
drop proc test/*
id num radio
---- ----------- -------------------------------
01 3 3%
02 4 7%
03 33 40%
04 12 52%(所影响的行数为 4 行)id num radio
---- ----------- -------------------------------
01 3 52%
02 4 49%
03 33 33%
04 12 45%(所影响的行数为 4 行)id num radio
---- ----------- -------------------------------
01 3 15%
02 4 19%
03 33 52%
04 12 12%(所影响的行数为 4 行)
*/