select identity(int,1,1) id,* into # from 你的表select ((select sum(b) from # where id<=tem.id and 字段c=tem.字段c)-(select sum(a) from # where id<=tem.id and 字段c=tem.字段c))/(select sum(b) from # where id<=tem.id and 字段c=tem.字段c) 结果 from # temdrop table #
解决方案 »
- SQLserver中对某一字段使用自增序列该怎么写?
- 插入数据报错String or binary data would be truncated
- 求一个SQL 语句
- sql 语句 有个问题! 谢谢! 急~
- 先进先出问题1
- 怎么取得一个连续数字中断开的最小的一个数字
- 高分求解多重嵌套查询的解决方案
- 多用户插入数据,返回与库里重复的记录??
- 兄弟们帮忙啊!SQL Server2000连接与安装的问题,谢谢了!
- SQL SERVER查询性能
- 如何让系统表不显示,只显示用户定义的表?
- 呵呵,win server 2003 的sqlserver 2000(sp3)终于连上了,感谢pengdali,请近来接分!
1 7 dd
3 8 dd
..................
X Y dd
2 4 rr
5 9 rr是这样,DD的记录不只是两条,只是当DD变化时才不对上面的记录求各除,重头开始,是这个意思吗?
帮帮我,谢谢了!!
其实是这样的,字段a字段b都是数字,我现在要按字段c分组,然后逐个记录进行求和,
即:(b-a)/b*100求出每个求和记录的百分率,即平常我们用到的西格玛求和公式
总之,几句话说不清,你能按上面的给我写一个吗?
a int,
b int,
c char(2))select * from #a
insert #a select 1,7,'dd'
insert #a select 3,8,'dd'
insert #a select 2,4,'rr'
insert #a select 5,9,'rr'select *, identity(int,1,1) as id into #t from #aselect a, b, c, (select (sum(cast(b as numeric(8,4))) - sum(cast(a as numeric(8,4)))) / sum(cast(b as numeric(8,4)))
from #t where id <= A.id and c = A.c) result
from #t as Aa b c result
----------- ----------- ---- ----------------------------------------
1 7 dd .857142
3 8 dd .733333
2 4 rr .500000
5 9 rr .461538
set @i=0
set @j=0
set @lastC=''drop table #temp
select *,cast(0 as numeric(18,6)) D into #temp from a0620
update #temp
set
@j=case when @lastC=c then @j+b else b end
,@i=case when @lastC=c then @i+a else a end
,d=((@j)-(@i))/(@j)
,@lastC=c
select * from #temp
---------------------------------------
a0620
--------
a,b,c
1.000000 7.000000 aaa
3.000000 8.000000 aaa
2.000000 4.000000 bbb
5.000000 9.000000 bbb#temp
----------------
a,b,c,d
1.000000 7.000000 aaa .857143
3.000000 8.000000 aaa .733333
2.000000 4.000000 bbb .500000
5.000000 9.000000 bbb .461538
的试过,得到的有些值正确,有些值就不对了!!
CREATE PROCEDURE sp_rpt_Material_Overspend_Underspend1
@enquiry int=0,
@workgroup varchar(100)='',
@stockcode varchar(9)='',
@year varchar(6)='',
@variance varchar(6)=''
as declare @sql varchar(2000),@sqlwhere varchar(500),@sqlworkgroup varchar(300),@sqlstockcode varchar(300)
declare @sqlyear varchar(300),@sqlvariance varchar(300)
if @enquiry=0
begin
set @sqlwhere='and T2_USAGE.actual_usage=''0'' and T3_USER_FORCAST.demand>=''1'''
end
else if @enquiry=1
begin
set @sqlwhere='and T2_USAGE.actual_usage>=''1'' and T3_USER_FORCAST.demand=''0'''
end
else if @enquiry=2
begin
set @sqlwhere='and (case isnull(T3_USER_FORCAST.demand,0) when 0 then 0 else convert(numeric(3),(T3_USER_FORCAST.demand-T2_USAGE.actual_usage)/T3_USER_FORCAST.demand*100) end) >=''15'''
end
else if @enquiry=3
begin
set @sqlwhere='and (case isnull(T3_USER_FORCAST.demand,0) when 0 then 0 else convert(numeric(3),(T3_USER_FORCAST.demand-T2_USAGE.actual_usage)/T3_USER_FORCAST.demand*100) end) <=''15'''
end
else
begin
set @sqlwhere=''
end if @workgroup=''
begin
set @sqlworkgroup=''
end
else
begin
set @sqlworkgroup='and T3_USER_FORCAST.work_group like '+ '''%'+@workgroup+'%'''
end
if @stockcode=''
begin
set @sqlstockcode=''
end
else
begin
set @sqlstockcode='and T1_STOCK_MASTER.stock_code like '+'''%'+ @stockcode+'%'''
end if @year=''
begin
set @sqlyear='and DATEDIFF(year,cast(T3_USER_FORCAST.period+''01'' as datetime),getdate())=''0'''
end
else
begin
set @sqlyear='and DATEDIFF(year,cast(T3_USER_FORCAST.period+''01'' as datetime),cast( ' +''''+@year+''''+'+''0101'' as datetime))=''0'''
end if @variance=''
begin
set @sqlvariance=''
end
else
begin
set @sqlvariance='and (case isnull(T3_USER_FORCAST.demand,0) when 0 then 0 else convert(numeric(3),(T3_USER_FORCAST.demand-T2_USAGE.actual_usage)/T3_USER_FORCAST.demand*100) end) ='+''''+@variance+''''
end
set @sql='
select distinct T1_STOCK_MASTER.class,
T1_STOCK_MASTER.stock_code,
T3_USER_FORCAST.work_group,
T1_STOCK_MASTER.item_name,
T1_STOCK_MASTER.abc,
T1_STOCK_MASTER.criticality,
T1_STOCK_MASTER.soh,
T6_ORDERS.invent_price,
T6_ORDERS.invent_value,
T3_USER_FORCAST.period,
T2_USAGE.actual_usage,
T3_USER_FORCAST.demand,
(case isnull(T3_USER_FORCAST.demand,0) when 0 then 0 else convert(numeric(3),(T3_USER_FORCAST.demand-T2_USAGE.actual_usage)/T3_USER_FORCAST.demand*100) end) AS Monthly_Variance,
identity(int,1,1) as id,
T4_PROJECTION.projection
into #TEMP1
from T1_STOCK_MASTER
inner join T3_USER_FORCAST
on T1_STOCK_MASTER.stock_code=T3_USER_FORCAST.stock_code
inner join T6_ORDERS
on T1_STOCK_MASTER.stock_code=T6_ORDERS.stock_code
inner join T2_USAGE
on T1_STOCK_MASTER.stock_code=T2_USAGE.stock_code
and T3_USER_FORCAST.period=T2_USAGE.period
inner join T4_PROJECTION
on T1_STOCK_MASTER.stock_code=T4_PROJECTION.stock_code
and T3_USER_FORCAST.period=T4_PROJECTION.period
where T1_STOCK_MASTER.category=''r''
and T1_STOCK_MASTER.stock_type=''1''
and T1_STOCK_MASTER.criticality<>''e''
'+@sqlwhere+@sqlyear+@sqlworkgroup+@sqlstockcode+@sqlvariance
+
'
select *, (select (sum(cast(demand as numeric(8,4))) - sum(cast(actual_usage as numeric(8,4)))) / sum(cast(demand as numeric(8,4)))
from #TEMP1 where id <= A.id and work_group = A.work_group) result
from #TEMP1 as Adrop table #TEMP1'
exec(@sql)
print @sql
GO