Mysql グループ毎に追加データに自動で連番を付けてINSERTする方法

Question

テーブル名「vegetables」

現在のテーブル
id item no date
1 トマト　1 2018-12-03
2 トマト　2 2018-12-03
3 トマト　3 2018-12-03
4 レタス　1 2018-12-03

次にINSERTするデータ
レタス 2018-12-03
トマト 2018-12-03
（5番目と6番目に追加でINSERT。※idはAUTO_INCREMENTです）

id item no date
1 トマト　1 2018-12-03
2 トマト　2 2018-12-03
3 トマト　3 2018-12-03
4 レタス　1 2018-12-03
5 レタス　2 2018-12-03
6 トマト　4 2018-12-03

の様に「date」と「item」のグループで「no」に連番で自動にINSERTする方法が分からなく困っております。
宜しくお願いいたします。

Answer 1

こんな感じ

• 元データ

create table vegetables(id int primary key auto_increment,item varchar(20),no int null,d date);
insert into vegetables values
(1,'トマト',1,'2018-12-03'),
(2,'トマト',2,'2018-12-03'),
(3,'トマト',3,'2018-12-03'),
(4,'レタス',1,'2018-12-03');

• 投入

insert into vegetables(item,d) values
('レタス','2018-12-03'),
('トマト','2018-12-03'),
('レタス','2018-12-03'),
('レタス','2018-12-03'),
('トマト','2018-12-03');

• 更新

insert into vegetables(id,no)
select * from(
select id,(select count(*)+1 from vegetables where item=t1.item and d=t1.d and id<t1.id) as no from vegetables as t1
) as sub
on duplicate key update no=values(no)

Comments

[efu_akio]

一度INSERTしてから連番をINSERTするんですね。全く思いも付きませんでした。
ありがとうございます。