sqlserver累计,sql 累计

sqlserver 2000怎么计算累计百分比

--sql 2000

declare @tb table(row int identity(1,1),故障总成件 varchar(100),数量 int,占比 float)

insert into @tb select * from tb

select 故障总成件,数量,占比,累计百分比=(select sum(占比) from @tb t2 where t2.row=t1.row) from @tb t1

--sql 2005

with tc as(

select row=row_number()over(order by getdate()),* from tb

),

cte as(

select *,累计百分比=cast(占比 as decimal(28,3)) from tc where row=1 union all

select t.row,t.故障总成件,t.数量,t.占比,cast(c.累计百分比+t.占比 as decimal(28,3)) from tc t join cte c on t.row=c.row+1

)

select * from cte

相关问答

Q1: 用SQLserver编写存储过程实现输入两个整数， 计算并输出他的累加和怎么写？

create proc sp_addtest

pi_a int,

pi_b int

as

begin

print pi_a + pi_b

end

Q2: 100分 求教SQLSERVER中的用游标遍历表中的列并实现累加输出。

你逻辑流程有问题 ，下面是你的逻辑流程，

读取下一条→加值→[判断是否结尾→读取下一条→加值]（循环）

而@@FETCH_STATUS返回3种状态，

0 FETCH 语句成功。

-1 FETCH 语句失败或此行不在结果集中。

-2 被提取的行不存在。

这样在游标移动到最后一行的时候，进行while判断@@FETCH_STATUS为0 ，进行一次加值，然后又直接进入while判断 这时候你还没有移动游标，@@FETCH_STATUS还是为0，又进行了一次加值，然后移动游标，进行判断@@FETCH_STATUS不为0，退出循环

所以应该修改逻辑为

读取下一条→[判断是否结尾→加值→读取下一条]（循环）

SQL语句调整如下

===========================================

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor into @id,@name

WHILE @@FETCH_STATUS = 0

BEGIN

set @finalname = @finalname + @name

FETCH NEXT FROM Employee_Cursor into @id,@name

END

CLOSE Employee_Cursor

==================================

以上语句实测试通过

先移动游标，然后就开始判断，为真进行加值 然后移动游标，这样就没问题了，

Q3: SQLserver数据库中,当日累计交易量查询语句怎么写?

select * from 表 WHERE 交易金额200000 and datediff(d,日期字段,getdate())=0

Q4: SQLServer2008 数据查询 累加问题

select x.BFactoryName,x.LineName,x.Month,y.BaseValue,x.CurValue,x.TotValue

from (

select b.BFactoryName,b.LineName,a.Month,--c.BaseValue,

sum(case when a.Month=b.Month then b.MonthValue else 0 end) as CurValue, --统计当月值

sum(case when a.Month=b.Month then b.MonthValue else 0 end) as TotValue --统计累计值

from (select Month=cast(1 as int) union select 2 union select 3 union select 4

union select 5 union select 6 union select 7 union select 8

union select 9 union select 10 union select 11 union select 12) a --生成月的基准值

join (select BFactoryName,LineName,Month,sum(Value)/12 as MonthValue

from T where FactoryName='F' and Year=2012 group by BFactoryName,LineName,Month) b on 1=1

group by b.BFactoryName,b.LineName,a.Month

) x

left outer join

(select BFactoryName,LineName,sum(Value)/12 as BaseValue--统计基准值

from T where FactoryName='F' and Year=2012-1 group by BFactoryName,LineName) y

on x.BFactoryName=y.BFactoryName and x.LineName=y.LineName