select insertの際、group byで連番を生成したい mysql5.7.19

Question

テーブルAには以下のようなデータがあります。

id	groupid	data	type
1	1	A	data1
2	1	B	data2
3	2	A	data1
4	3	B	data2

これらのデータをテーブルBにgroupid毎にまとめてselect insertしてさらにそれに連番を付与したいです。

望む結果
テーブルB

id（連番）	groupid	data1	data2
1	1	A	B
2	2	A
3	3	B

当初想定していたSQL

SET @num=0;
INSERT INTO tableB (
  id,
  groupid,
  data1,
  data2,
) 
 select 
  (@num:=@num+1),
  (SELECT DISTINCT tableA.groupid FROM tableA WHERE groupid = group.id ),
  (SELECT tableA.dataA FROM tableA WHERE type = 'dataA' AND groupid = group.id),
  (SELECT tableA.dataB FROM tableA WHERE type = 'dataB' AND groupid = group.id)
  FROM group INNER JOIN tableA on group.id = tableA.groupid
  GROUP BY tableA.groupid

上記SQLだと
テーブルB

id（連番）	groupid	data1	data2
1	1	A	B
3	2	A
4	3	B

groupテーブルが存在しているのでそこから集約してinsertを考えていましたが、連番付与する際にgroupbyが理由で抜け番となってしまいます。ROW_NUMBERが使えれば解決できるのかなとは思ったのですが、いかんせんmysqlのverが古く・・・

ご教授いただければ幸いです。<m(__)m>

Comments

[K_Y_M]

テーブルBのidにAUTO_INCREMENTを設定する事は可能でしょうか？

[Kaso]

ありがとうございます。
データ移行の際に一時的に設定するのであれば可能ですが、
基本的には設定しない方向で出来るのであれば　といった感じです。

Answer 1

これは如何でしょうか

SET @num=0;
INSERT INTO tableB (
  id,
  groupid,
  data1,
  data2,
)
select 
  (@num:=@num+1),
  x.groupid,
  x.dataA,
  x.dataB
from
(
  select
  (SELECT DISTINCT tableA.groupid FROM tableA WHERE groupid = group.id ) as groupid,
  (SELECT tableA.dataA FROM tableA WHERE type = 'dataA' AND groupid = group.id) as dataA,
  (SELECT tableA.dataB FROM tableA WHERE type = 'dataB' AND groupid = group.id) as dataB
  FROM group INNER JOIN tableA on group.id = tableA.groupid
  GROUP BY tableA.groupid
) as x

Answer 2

groupidを入れれば良いと思います。groupidが連番でない場合は

• groupidでソートして連番を処理するか
• groupidでランク付け処理をする

のが妥当です

Comments

[Kaso]

ありがとうございます！
ランク付けというキーワードで以下のように設定し解決しました。
FIND_IN_SET( group .id, ( SELECT GROUP_CONCAT(group .id ORDER BY group .id ASC) FROM group )),