Select句をwhile文を使って作成したい

Question

前提・実現したいこと

過去に質問され、回答されているコードをMySQLで実行できるように変更したいです。
EXECがMySQL 8.0では動かなかったり、SET QUOTED_IDENTIFIER OFFは存在しなかったりと所々違うので修正できないです。
詳しい方、お願いします。
過去の質問

自分の状況

ちなみに自分は以下のようなコードを上記の回答を見ながらwhile文で作成したいと考えています。
今回は上記の質問への回答のMySQL版がわかればそれを見ながら自分で作業しようと考えています。そのため、自分のコードは一部抜粋で表示しています。

###実装してみた結果
回答いただいた内容をもとに実装しましたが、while文の中のselect文が実行されませんでした。
1つのtemporary tableにデータを収めたい場合はどうすれば良いでしょうか？

DROP TEMPORARY TABLE IF EXISTS tempo_table2;
DROP PROCEDURE IF EXISTS previous;
DELIMITER //
create procedure previous(in p_Limit int)
Begin   
    SET @sql1 := CONCAT("create  temporary table tempo_table2",
						' select',
						' text_date',
                        ' ,cd_fromName',
                        ' ,cd_text',
                        ' ,command_content',
                        ' ,cd_text_action',
                        ' from tempo_table;');
    prepare sql1 FROM @sql1;
    Execute sql1;
    Deallocate prepare sql1;
    SET @CNT = 1;
	while p_limit >= @CNT Do
		Set @sql2 := CONCAT("select",
                        ' ,lag(cd_text_action,',@CNT,') over(order by cd_fromName,text_date) as previous_action',@CNT,
                        ' ,lag(cd_fromName,',@CNT,') over(order by cd_fromName,text_date) as previous_Name',@CNT,
                        ' from tempo_table;');
		SET @CNT = @CNT + 1;
	prepare sql2 FROM @sql2;
    Execute sql2;
    Deallocate prepare sql2;
	End while;
End;
//
DELIMITER ;
call previous(20);
select * from tempo_table2;

該当のソースコード

lag(cd_text_action,1)
lag(cd_text_action,2)
lag(cd_text_action,3)
のように数字を1つずつ増やしながら、列の作成をしていきたいです。

create temporary table tempo_table2
select 
    text_date,
    cd_fromName,
    cd_text,
    command_content,
    cd_text_action,
    #過去に入力したactionをn回分保存する
    lag(cd_text_action,1) over(order by cd_fromName,text_date) as previous_action1,
    lag(cd_text_action,2) over(order by cd_fromName,text_date) as previous_action2,

こちらも同様にprevious_action1,previous_action2,previous_action3
と
then 1, then 2, then 3のように数字を一つずつ増やしていきたいです。

case 
   when previous_action1 in("menu_cmd_01_01","menu_cmd_02_02") AND            cd_fromName = previous_Name1 then 1
   when previous_action2 in("menu_cmd_01_01","menu_cmd_02_02") AND            cd_fromName = previous_Name2 then 2

補足情報（FW/ツールのバージョンなど）

MySQL 8.0

Comments

[sazi]

列を動的に増やすという事は動的SQLの生成になるので、ストアドかフロントのプログラムでSQL文を文字列で組み立てるという事になりますけど、それは理解されていますか？

[iiiiiiiikkkk]

はい、動的SQLの生成になることは理解できています。
ストアドプロシージャでSQL文の文字列を組み立てた上で、プロシージャをcallすることが必要なのではないかと考えています。

しかしながら、私ではプロシージャで組み立てたSQL文をどのように実行すれば良いのかわからないです。
リンク先で言うとEXEC(@sql1+@sql2+,,,)が該当していますが、MySQLではEXECが動作しなかったです。

[sazi]

列をどこまで増やすかは決まっているのですか？
質問のリンク先では24時間という範囲が決まってますけど、この質問では？

[iiiiiiiikkkk]

明確には決めていないのですが、100〜1000の範囲で列を増やしたいと考えています。

Answer 1

saziさんに指摘していただいたミスを修正してみたら動きました！！

DROP TEMPORARY TABLE IF EXISTS tempo_table2;
DROP PROCEDURE IF EXISTS previous;
DELIMITER //
create procedure previous(in p_Limit int)
Begin   
    SET @sql1 := CONCAT("create  temporary table tempo_table2",
						' select',
						' text_date',
                        ' ,cd_fromName',
                        ' ,cd_text',
                        ' ,command_content',
                        ' ,cd_text_action');
    SET @CNT = 1;
    SET @sql2 := '';
	while p_limit >= @CNT Do
		Set @sql2 := CONCAT(@sql2,' ,lag(cd_text_action,',@CNT,') over(order by cd_fromName,text_date) as previous_action',@CNT,
                        ' ,lag(cd_fromName,',@CNT,') over(order by cd_fromName,text_date) as previous_Name',@CNT);
		SET @CNT = @CNT + 1;
	End while;
    SET @sql3 := CONCAT(@sql1,@sql2,' from tempo_table;');
    prepare sql3 FROM @sql3;
    Execute sql3;
    Deallocate prepare sql3;
End;
//
DELIMITER ;
call previous(20);
select * from tempo_table2;

Answer 2

動的SQLについて以下参考
MySQL ストアドプログラム内での動的SQL
MySQLの動的SQL使って検索するカラム名を動的に指定する

ループの終了条件が可変という事なので、先ずは再帰で取得してからの編集が良い気がします。
MySQL 8.0の再帰With句のサンプル集

Comments

[iiiiiiiikkkk]

ご回答ありがとございます。動的SQL文の参考URLについて非常に理解しやすかったです。
追加で質問なのですが、
1つ目のURLで示されている手法を一部で実装してみましたが、temporary tableを使用しているためか、while文の中のselect文が実行できません。
コードをアップするのでレビューをいただけないでしょうか？

[sazi]

コード見ましたけど、単に@CNTがカウントアップされて、最後のカウントのSQL文が実行されているだけのように見えます。
ループ内では生成したSQLは上書きされているだけ

[iiiiiiiikkkk]

おそらく行っているとは思います。。。
おっしゃっていることを正確に理解できていないのが正直なところですので、質問本文の「実装してみた結果」のコードを見ていただく事は可能でしょうか？
コードはtempo_tableという一時テーブルを作成し、それを読み込んで作業数rものとなっています。
この後にも作業が続くので、tempo_table2として作成したいという状況です。

[sazi]

コメント前後しました。
ループではlagの部分をSQLとして追加するのが、やりたいことではないのでしょうか。

[iiiiiiiikkkk]

はい、その通りです。
なるほど、そのような状況になっていたのですね、、、

どのように改善すればよいか実際のコードで示していただくことは可能でしょうか、、？

[iiiiiiiikkkk]

すみません！おっしゃっていた意味を理解して、自力で修正できました！