with
my_ranking as (
select s.*,
@rownum := @rownum + 1 as _rank
from salsa s,
(select @rownum := 0) r
)
update salsa as s1
set new_column = (
select _rank
from my_ranking as s2
where s1.id = s2.id
limit 1
);
with
my_ranking as (
select *,
rank() over(order by id) as _rank
from salsa
)
update salsa as s1
set new_column = (
select _rank
from my_ranking as s2
where s1.id = s2.id
limit 1
);
drop table if exists test;
create table test(id int primary key auto_increment, num int default 0);
delimiter //
drop procedure if exists makerows//
-- let's create a procedure to generate necessary rows.
create procedure makerows()
begin
declare c int default 1;
lp:loop
if c>250 then
leave lp;
end if;
if c<=11 then
insert test values(default,c);
else insert test (id) values(default);
end if;
set c=c+1;
end loop lp;
end//
delimiter ;
call makerows; -- now we have the test table with the id from 1 to 250 and the num from 1 to 11 with the rest being 0
-- here is the user variable trick to generate row id as required. Note we set the initial @row_id to -1 so we can calculate the first @row_id to be 0 for the first row (id=11).
select id ,@row_id:=@row_id+1 as row_id
from test,(select @row_id:=-1) t
where id>=11 order by id ;
-- the query above is used to get a derived table which shall be joined to the base table, so we can update the required num column
update test t1
join
(select id ,@row_id:=@row_id+1 as row_id
from test,(select @row_id:=-1) t
where id>=11 order by id) t2
on t1.id=t2.id
set t1.num=t2.row_id
;
-- now we have the test table in which the id from 11 to 250 have the num column values from 0 to 239
select * from test;
3条答案
按热度按时间r8xiu3jd1#
有两种方法(我可以想到)来解决这个问题。您可以使用窗口函数,也可以使用变量。
假设您有以下测试表:
使用变量
变量一非常直接。尽管你对哪一个先发的控制权较小。此部件改编自row_number() in mysql。
@rownum
变量用于跟踪行的当前秩。参见小提琴:https://www.db-fiddle.com/f/Jmn5x34WXaBJq7oyemtXp/0
使用窗口函数
window functions附带
rank()
,我们将在本次使用。它至少需要mysql版本8才能使用。我询问参考点如何对行进行排序的原因是,
rank()
需要分区才能工作,否则每个人都是排名1的。重要的部分是:要确定行的“位置”,可以根据需要使用
id
或其他列。参见小提琴:https://www.db-fiddle.com/f/nwLv9R7weQt4e5RhUbgaUL/0
注:
vdgimpew2#
如果使用的是不支持CTE和窗口函数的经典版本(8.0之前),我们必须找到一个解决方法。为了解决这个问题,我们可以使用用户变量技巧。下面是演示它的完整步骤。
1hdlvixo3#
创建一个用户定义的变量(例如
@rownumber
)以增加1
的值。结果:
演示