mysql根据下一行的数据更新行

Question

我在 mysql 中有这个表:

name         ID
B1            1
B2            1
B3            1
B4            2
B5            2
B6            2
A1            3
A2            3
A3            3

我想将数据添加到列中，通知 id 将在下一行更改。像这样:

name       beforeChange     ID
 B1          NO               1
 B2          NO               1
 B3          YES              1
 B4          NO               2
 B5          NO               2
 B6          YES              2
 A1          NO               3
 A2          NO               3
 A3          NO               3
 A4          NO               3

有没有办法在sql中做到这一点？谢谢!

Answer 1

丑陋无比，从效率的角度来看可能远非理想，但似乎可行:

create table myTable (id int(10) unsigned not null,name varchar(10));

insert into myTable (id,name) values (1,'B1');
insert into myTable (id,name) values (1,'B2');
insert into myTable (id,name) values (1,'B3');
insert into myTable (id,name) values (2,'B4');
insert into myTable (id,name) values (2,'B5');
insert into myTable (id,name) values (2,'B6');
insert into myTable (id,name) values (3,'A1');
insert into myTable (id,name) values (3,'A2');
insert into myTable (id,name) values (3,'A3');
insert into myTable (id,name) values (3,'A4');

select a.id,
case 
when b.id is null then 'NO' 
when b.id = (select max(id) from myTable) then 'NO' -- < handle last line of results set
else 'YES' end as beforeChange,a.name
from
-- rank within id
(
select mt.id,mt.name,
case mt.id when @curId then @curRow := @curRow + 1 
          else @curRow := 1 and @curId := mt.id END as rank
from myTable mt
join (select @curRow := 0, @curId := -1) r
order by id,name asc
) a
left outer join
-- max rank by id
(
select t.id,max(t.rank) as maxRank
from (
select mt.*,
case mt.id
        when @curId 
        then @curRow := @curRow + 1 
        else @curRow := 1 and @curId := mt.id END
      as rank
from myTable mt
join (select @curRow := 0, @curId := -1) r
order by id,name asc
) t 
group by t.id
) b on a.id = b.id and b.maxRank = a.rank;