-- 一.如何用行号除以总行数 select 行号/(select count(*) from tab) from tab-- 二.累计每行的和 select sum(数量) from tab
declare @t table (id int) insert into @t select 1 union all select 2 union all select 3 union all select 4 union all select 5select id,ltrim(id*100/(select count(1) from @t))+'%' as 计算 from @t /* id 计算 ----------- ------------- 1 20% 2 40% 3 60% 4 80% 5 100% */
select 行号/@@rowcount from 表第2 个就不知道老
declare @t table (数量 int) insert into @t select 100 union all select 200 union all select 300 union all select 400select *,(select sum(数量) from @t where 数量<=t.数量) as 合计 from @t t /* 数量 合计 ----------- ----------- 100 100 200 300 300 600 400 1000 */
因为你的行号不明确(不知道里面的数据是怎么排序的,所以行号不好取) 当然你可以用row_number()over(order by *)手动来添加行号列 我们假定已经取得行号 行号 1 2 3 4 5 select a.行号, 计算=ltrim(cast(a.行号/cast((select count(b.行号) from t1 b) as decimal(4,2)) as decimal(4,2))*100)+'%' from t1 a /* 行号 计算 --- --- 1 20.00% 2 40.00% 3 60.00% 4 80.00% 5 100.00% */第二题,缺少标识字段,那就用游标一个一个来取create table t1(数量 int,合计 int) insert t1 select 100,100 union all select 200,300 union all select 300,600 union all select 400,1000 godeclare @数量 int declare @总计 int declare @tb table(数量 int,总计 int) set @总计=0 declare curTemp cursor for select 数量 from t1 open curTemp fetch next from curTemp into @数量 while @@fetch_status=0 begin set @总计=@总计+@数量 insert @tb (数量,总计) values(@数量,@总计) fetch next from curTemp into @数量 end close curTemp deallocate curTemp select * from @tb /* 数量 总计 --- --- 100 100 200 300 300 600 400 1000 */ go drop table t1
-- 一.如何用行号除以总行数
select 行号/(select count(*) from tab) from tab-- 二.累计每行的和
select sum(数量) from tab
declare @t table (id int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5select id,ltrim(id*100/(select count(1) from @t))+'%' as 计算 from @t
/*
id 计算
----------- -------------
1 20%
2 40%
3 60%
4 80%
5 100%
*/
declare @t table (数量 int)
insert into @t
select 100 union all
select 200 union all
select 300 union all
select 400select *,(select sum(数量) from @t where 数量<=t.数量) as 合计 from @t t
/*
数量 合计
----------- -----------
100 100
200 300
300 600
400 1000
*/
当然你可以用row_number()over(order by *)手动来添加行号列
我们假定已经取得行号
行号
1
2
3
4
5
select
a.行号,
计算=ltrim(cast(a.行号/cast((select count(b.行号) from t1 b) as decimal(4,2)) as decimal(4,2))*100)+'%'
from t1 a
/*
行号 计算
--- ---
1 20.00%
2 40.00%
3 60.00%
4 80.00%
5 100.00%
*/第二题,缺少标识字段,那就用游标一个一个来取create table t1(数量 int,合计 int)
insert t1
select 100,100 union all
select 200,300 union all
select 300,600 union all
select 400,1000
godeclare @数量 int
declare @总计 int
declare @tb table(数量 int,总计 int)
set @总计=0
declare curTemp cursor for
select 数量 from t1
open curTemp
fetch next from curTemp into @数量
while @@fetch_status=0
begin
set @总计=@总计+@数量
insert @tb (数量,总计) values(@数量,@总计)
fetch next from curTemp into @数量
end
close curTemp
deallocate curTemp select * from @tb
/*
数量 总计
--- ---
100 100
200 300
300 600
400 1000
*/
go
drop table t1