レコードの2文字目をアルファベット（aから昇順）に置換するSQL文（追加質問２）

Question

###前提
MySQLにて、データベースに入っているレコードに対し、
2文字目をアルファベット（aから昇順）に置換する
SQL文を作成しています。

###現状の動作と実現したい動作イメージ
・SQL実行前
ID / 枝番/ ユーザID
A001 / 001 / E001
A001 / 002 / E002
： / ： / ：
A001 / 026 / E026
A002 / 001 / F001
A002 / 002 / F002
A003 / 001 / F002

・SQL実行後（現状）
ID / 枝番/ ユーザID
Aa01 / 001 / E001
Ab01 / 002 / E002
： / ： / ：
Az01 / 026 / E026
A02 / 001 / F001
A02 / 002 / F002
A03 / 001 / F002

※上から順にaからzを振られているが、
27番目以降は2文字目にnullが置換されている

・SQL実行後（理想）
ID / 枝番/ ユーザID
Aa01 / 001 / E001
Ab01 / 002 / E002
： / ： / ：
Az01 / 026 / E026
Aa02 / 001 / F001
Ab02 / 002 / F002
Aa03 / 001 / F002

※ユーザIDとID、セットでPK扱い。
27番目以降でもユーザIDとIDのセットが新しくなっていれば、
2文字目を「a」から開始する。
※ユーザIDとIDのセットが27以上存在する場合は
別箇所でエラー処理する予定なので今回は考慮していただかなくて大丈夫です。

###現在試していること
ストアドプロシージャで、
IDとユーザIDでgroup byし、その結果をカーソル変数に代入、
ループしながら2文字目を書き換える、というSQL文で
実現しようとしています。

###現在作成しているSQL文

delimiter //
create procedure alphabet()

BEGIN
	DECLARE done INT DEFAULT 0; 
    DECLARE _count INT;
	DECLARE cur CURSOR FOR #カーソル変数
		SELECT
			count(*)
		FROM
			sampleTable
		GROUP BY ID,USER_ID
        ORDER BY branch ASC; # branch：枝番
	DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
	WHILE done !=1 DO
		FETCH cur INTO _count;
        IF _count > 1 AND _count < 27 THEN
			SET @i:=0;
			UPDATE
				sampleTable AS tbl3,
				(SELECT 
					MID('abcdefghijklmnopqrstuvwxyz',@i:= @i+1,1) AS ID,
					USER_ID
				FROM
					(SELECT @i:= 0) AS dum,
					
					(SELECT DISTINCT
						USER_ID 
					 FROM
						sampleTable ORDER BY 1
					) AS tbl2
				) AS tbl1
			SET
				tbl3.ID = concat(MID(tbl3.ID,1,1),tbl1.ID,MID(tbl3.ID,3,2))
			WHERE
				tbl3.USER_ID = tbl1.USER_ID;
			
		ELSE UPDATE sampleTable SET ID = ID;
        END IF;
	END WHILE;
    CLOSE cur;
END
//

CALL alphabet()

###追記
現在、ストアドプロシージャで実現しようとしていますが、
他の方法があれば、特にストアドプロシージャにはこだわりません。

以上です。
複雑で申し訳ないですが、
ご不明点等ございましたらご指摘ください。

宜しくお願い致します。

Comments

[yambejp]

A001の26番めのでーたが「AZ01 / 026 / E026」となっていますが「Az01」でよいですか？

[退会済みユーザー]

ご指摘ありがとうございます。「Az01」が正しいです。（本文も修正しました）

Answer 1

asahina_devさんの、ユーザ変数を使った方法ですが

MySQLのマニュアルと
OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村明治さんによると
1ステートメントでユーザ変数を複数回使用した場合の
ユーザ変数の評価順序は未定義です。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

具体的には、
@i:= 0
が全ての @i:= @i+1の評価よりも、前に評価されることが保証されませんので、
update結果は、IDに重複が発生する可能性があります。

要するに、(select ＠i:=0)とのクロスジョインにおいて、
(select ＠i:=0)の行数は評価するけど
select句は、後回しに評価した場合ですね。

Answer 2

delimiter //
create procedure alphabet()

BEGIN
    DECLARE done INT DEFAULT 0; 
    DECLARE _count INT;
    DECLARE cur CURSOR FOR #カーソル変数
        SELECT
            count(*)
        FROM
            sampleTable
        GROUP BY ID,USER_ID
        ORDER BY branch ASC; # branch：枝番
    DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    WHILE done !=1 DO
        FETCH cur INTO _count;
        IF _count > 1 AND _count < 27 THEN
            SET @i:=0;
            UPDATE
                sampleTable AS tbl3,
                (SELECT 
                 -- MID('abcdefghijklmnopqrstuvwxyz',@i:= @i+1,1) AS ID,
                    MID('abcdefghijklmnopqrstuvwxyz',MOD(@i:= @i+1, 26),1) AS ID,
                    USER_ID
                FROM
                    (SELECT @i:= 0) AS dum,

                    (SELECT DISTINCT
                        USER_ID 
                     FROM
                        sampleTable ORDER BY 1
                    ) AS tbl2
                ) AS tbl1
            SET
                tbl3.ID = concat(MID(tbl3.ID,1,1),tbl1.ID,MID(tbl3.ID,3,2))
            WHERE
                tbl3.USER_ID = tbl1.USER_ID;

        ELSE UPDATE sampleTable SET ID = ID;
        END IF;
    END WHILE;
    CLOSE cur;
END
//

CALL alphabet()

でいけるような（ループさせるなら MOD であまりを計算）

Comments

[退会済みユーザー]

以前の質問から引き続きご回答ありがとうございました。
本当に助かりました！
また宜しくお願い致します。

Answer 3

こんな感じでどうでしょう?

UPDATE sampleTable
SET
	  ID = MID('abcdefghijklmnopqrstuvwxyz', cnt, 1)
FROM (
	SELECT
		  A.ID
		, A.userID
		, count(*) + 1 AS cnt
	FROM sampleTable A
	JOIN sampleTable B
	ON A.ID = B.ID
	AND A.userID > B.userID
) C
WHERE sampleTable.ID = C.ID
AND sampleTable.userID = C.userID

---

KotoriMaturiさんの指摘を受けて修正しました
(GROUP BYもぬけてましたし)

UPDATE
	  sampleTable T1
	, (
	SELECT
		  A.ID
		, A.userID
		, count(*) + 1 AS cnt
	FROM sampleTable A
	JOIN sampleTable B
	ON A.ID = B.ID
	AND A.userID > B.userID
	GROUP BY
		  A.ID
		, A.userID
	) T2
SET
	  T1.ID = MID(T1.ID, 1, 1)
		+ MID('abcdefghijklmnopqrstuvwxyz', T2.cnt, 1)
		+ MID(T1.ID, 3, 1)
WHERE T1.ID = T2.ID
AND T1.userID = T2.userID

UPDATEのFROM副問い合わせはすっかりSQLServerのノリで書いてましたf(^-^;

---

追記
そのまま残すべきところを忘れていたので追加しました

Comments

[退会済みユーザー]

ご回答ありがとうございます！

ただ、4行目のFROMでシンタックスエラーが出ています。
（MySQL Workbenchにて確認しております）
お手数をおかけして申し訳ございませんが、ご確認いただけますでしょうか。

何卒宜しくお願い致します。

[Panzer_vor]

> kutsulogさん
UPDATEのFROM句はPostgreSQLのベンダ拡張のようです。
MySQL用に置き換える必要がありそうですね

[Panzer_vor]

再び横槍ですが、
UPDATE句でのFROM指定はSQL Serverのベンダ拡張でもあるんですね。

それにしてもUPDATEの書き方はベンダ毎に差異があるので地味に厄介ですね…^^;

[退会済みユーザー]

> kutsulogさん
ご回答ありがとうございます！
a～zの二週目で期待していた動きを実現できなかったので。
私の仕様説明が分かりにくいなか、考えていただいたのに申し訳ございません。

>KotoriMaturiさん
私の質問に補足いただき、ありがとうございました！
PostgreSQLの書き方ということも分からず質問しておりました。。

Answer 4

たとえばこんな感じでどうでしょう？
ユーザーIDは関係なさそうなので省略しました

元データ

create table sampleTable(ID VARCHAR(10),枝番 VARCHAR(10),unique(ID,枝番));
insert into sampleTable values('A001','001'),('A001','002'),('A001','003')
,('A001','004'),('A001','005'),('A001','006'),('A001','007'),('A001','008')
,('A001','009'),('A001','010'),('A001','011'),('A001','012'),('A001','013')
,('A001','014'),('A001','015'),('A001','016'),('A001','017'),('A001','018')
,('A001','019'),('A001','020'),('A001','021'),('A001','022'),('A001','023')
,('A001','024'),('A001','025'),('A001','026'),('A002','001'),('A002','002')
,('A003','001');

UPDATE

update sampleTable as t1
,(select ID,枝番,char((select count(*)+97 from sampleTable as t3 where t3.ID=t4.ID and t3.枝番<t4.枝番)) as rank
from sampleTable as t4) as t2
set t1.ID=concat(substr(t1.ID,1,1),t2.rank,substr(t1.ID,3,3))
where t1.ID=t2.ID and t1.枝番=t2.枝番;

Comments

[退会済みユーザー]

ご回答ありがとうございます！
改めて私の質問文を読むと確かにユーザIDがいらないように読めますね、、
仕様説明に曖昧な部分があり、申し訳ございませんでした。。

Answer 5

よく質問文を読むと前回と要件が異なっていたのですね＾＾；
あれから再考してみましたが、
他の回答者さんの回答以外で、これだというクエリが力及ばず思いつきませんでした。

ただサンプルにあげてるデータだけを見ると、
0. IDが切り変わるタイミングで採番をリセットする
0. さらに言うとID毎に枝番が振られる

上記ルールが成り立ってるように見えるので、

• 登録された枝番が必ず1から始まる
• 枝番には欠番が存在しない

が保証されているのであれば難しく考えず、
単純に枝番を数値にキャストして、
その数値を基にアルファベットを取り出すで良いような気はします。

※以下履歴のために残してますが、今回の要望にはかなっていないため無視して下さい

アルファベット置き換え用テーブルを追加して･･･

CREATE TABLE LETTERS(
   SEQNO INTEGER(3)    -- 連番
,  LETTER VARCHAR(1)   -- アルファベット
)

連番には0～25にa~zを設定し、
SEQNO LETTER
0 a
1 b
** -- 中略 -- **
25 z

以下の感じのクエリでどうでしょうか？
ただ現在、MySQLでの検証環境がないのでエラーが出るかもしれないです…

SET @row = -1;
UPDATE table,
    ( 
        SELECT
            T2.*
        ,   @row:=@row + 1 AS ROW_NUM
        FROM
            ( 
                SELECT
                    T1.MIN_ID
                    , T1.UPD_KEY
                FROM
                    ( 
                        SELECT
                            MIN(ID1) AS MIN_ID
                            , ID2 AS UPD_KEY 
                        FROM
                            table 
                        GROUP BY
                            ID2 
                    ) T1
                ORDER BY T1.MIN_ID
            ) T2
    ) T3
    INNER JOIN LETTERS T4
        ON MOD(T3.ROW_NUM, 25) = T4.SEQNO
SET
    ID1 = CONCAT(SUBSTR(ID1, 1, 1), T4.LETTER, SUBSTR(ID1, 3)) 
WHERE
    ID2 = T4.UPD_KEY

・追記
MOD(T1.ROW_NUM, 25)と文字列変換テーブルの連番で結合かけてるので、
zまで採番が終わるとaから循環し直す仕様にしてます。

Comments

[退会済みユーザー]

前回に引き続きご回答いただき、ありがとうございます。
何度も考えていただき大変恐縮です。
仕様説明が分かりにくく申し訳ございませんでした。。