sql中关于统一列中数值相除的问题
发布网友
发布时间：2023-03-18 04:21
共5个回答
热心网友
时间：2023-05-13 16:16
case when A.rown=1 then A.bb/2 else A.bb/B.bb end from
--A.bb/B.bb当前列bb除以上一列BB是这个意思么
(select *, row_number() over(order by id) as num,
row_number() over(partition by id order by id) as rown
--按id计数,并且id改变重新计数每个id的第一个都除以自身,是这个意思么?
from table1) as A left join
(select *, row_number() over(order by id) as num,
row_number() over(partition by id order by id) as rown
from table1) as B
on A.num = B.num + 1
热心网友
时间：2023-05-13 16:16
sql2000的update和select都可以使用变量赋值，使用这点可以比较有效率地实现很多循环计算问题，比如LZ这题
create table table1(id int,bb float,mingxi int,ee float)insert into table1(id,bb,mingxi)
select 1,10,1
union all select 1,100,2
union all select 1,80,3
union all select 2,20,1
union all select 2,40,2
union all select 2,20,3
union all select 3,10,1
union all select 3,20,2
declare @id int,@ee float,@bb float
update table1 set @ee=case when isnull(@id,-1)=ID then bb/@bb else bb/2 end,ee=@ee,@bb=bb,@id=id
如果数据顺序是默认按 id 和 mingxi从小到大排列的话，一句update即可实现此功能
如果原始数据不是按id和mingxi从小到大排列，不能直接update，也可以通过先将数据插入临时表后再更新，如
select * into #table1 from table1 order by id,mingxideclare @id int,@ee float,@bb float
update #table1 set @ee=case when isnull(@id,-1)=ID then bb/@bb else bb/2 end,ee=@ee,@bb=bb,@id=id
最后再把#table1的ee根据id和mingxi写回table1
热心网友
时间：2023-05-13 16:17
else (select top 1 bb from table1where id=a.id and mingxi<a.mingxi order by mingxi desc) end
from table1 a
热心网友
时间：2023-05-13 16:17
(select a.*, a.row_number() as rn from table a order by id,mingxi) a left join
(select a.*, a.row_number() as rn from table a order by id,mingxi) b on (a.id=b.id and a.rn=b.rn+1)
自连接，用行号错开一个连接自己。如果b.bb为空的那就表示第一行，就除以2
热心网友
时间：2023-05-13 16:18
