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

Question

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

先に質問させていただいたおかげで、
上記動作はできるようになったのですが、さらに、
同じID2に対応するID1には同じアルファベットを付与する、
という動作を実現したいです。

※Z以降は別処理にてエラー対応予定のため今回は考慮不要です。
※処理に対して新規テーブルの作成が必須であれば作成致します。

###実現したい動作イメージ
・SQL実行前
ID1 / ID2
A0001 / S001
A0002 / S001
A0003 / S002
A0004 / S002
A0005 / S003

・SQL実行後（現状）
ID1 / ID2
Aa001 / S001
Ab002 / S001
Ac003 / S002
Ad004 / S002
Ae005 / S003

・SQL実行後（理想）
ID1 / ID2
Aa001 / S001
Aa002 / S001
Ab003 / S002
Ab004 / S002
Ac005 / S003

###現状

SET @cnt=0;
UPDATE table
  SET ID1=concat(mid(ID1,1,1),
                 mid('abcdefghijklmnopqrstuvwxyz',(@cnt:=@cnt+1),1),
                 mid(ID1,3,3));

ここまで複雑な動作はMySQLでは難しいでしょうか。。
宜しくお願い致します。

Comments

[jm1156]

それだとZをこえた後にエラーになりませんか？

[退会済みユーザー]

ご指摘ありがとうございます。Z以降は別処理にてエラー対応予定のためSQL処理に含めていませんでした！分かりにくくてスミマセンでした

Answer 1

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

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

具体的には、
concat関数での
if(@col=ID2,@row,(@row:=@row+1))
ID2=(@col:=ID2)
のどっちを先に評価するかは未定義ですので、結果が保証されません。

結果が保証されないといえば、
OrderBy句がないと出力順が保証されないのは有名ですね。

Comments

[A.Ichi]

ご指摘有難うございます。パラメータ評価はconcat前のmysqlが行い実行順番は無保証だと言う事ですね。勉強します。

Answer 2

更新KEYが有るのならできそう

update table t1,
 (select @i:=@i+1 as row, 
 concat(mid(ID1,1,1),substr('abcdefghijklmnopqrstuvwxyz',@i,1),mid(ID1,3,4)) as ID1,
 ID2 from (select @i:=0) as dum,(select distinct ID1, ID2 from table) t3) t2
 set t1.ID1=t2.ID1 where t1.ID2=t2.ID2;

間違えておりました訂正（一応動く）

update table t1,
 (select @i:=@i+1 as row, 
  substr('abcdefghijklmnopqrstuvwxyz',@i,1) as ID1,
  ID2 from (select @i:=0) as dum,(select distinct ID2 from table order by 1) t3) t2
 set t1.ID1=concat(mid(t1.ID1,1,1),t2.ID1,mid(t1.ID1,3,4))
 where t1.ID2=t2.ID2;

改良版(結果は一緒）

update table t1,
 (select 
  substr('abcdefghijklmnopqrstuvwxyz',@i:=@i+1,1) as ID1,
  ID2 from (select @i:=0) as dum,(select distinct ID2 from table order by 1) t3) t2
 set t1.ID1=concat(mid(t1.ID1,1,1),t2.ID1,mid(t1.ID1,3,4))
 where t1.ID2=t2.ID2;

ご指摘を反映しようと、せめてID2の順番でabc...になる様にしましたが、後ろでS001が現れると困る。

こんな事もMySQLは出来るんですね！（改定=>無保証）

SET @row=0;
SET @col=null;
UPDATE tableX
  SET ID1=concat(mid(ID1,1,1),
        mid('abcdefghijklmnopqrstuvwxyz',if(@col=ID2,@row,(@row:=@row+1)),1),
        mid(ID1,3,4)), ID2=(@col:=ID2);

最後のはMysqlの関数を評価する順番が保障されていないとのご指摘を受けましたので、無効としてください。１ステートメント中の変数評価の順番は基本的には保障されていない為、最後の部分が先に行われる事が有り得ると言う事です。ご指摘有難うございます。

Comments

[asahina_dev]

@i:=@i+1 as row が上から順に動くとは限らないと前突っ込みが入ったことを言っておこう

あった
http://qiita.com/hmuronaka/items/1afc132ddf400363efc2

ユーザ変数を使う方法ですが
MySQLのマニュアルと
OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村明治さんによると
MySQLのユーザ変数は評価順序が未定義です。
qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98
なので結果が保証されません。

[退会済みユーザー]

A.Ichi様、元質問から続けてご回答いただき、ありがとうございます。
記載いただいたSQLで期待通りの動作を実現できました！
本当にありがとうございます。

また、asahina_dev様もサポート、ありがとうございます。
評価順序を意識したこともありませんでした。。

今後とも宜しくお願い致します。

[退会済みユーザー]

幸運にも期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
業務では問題外の品質なので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

Answer 3

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

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

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

PostgreSQLで以下の感じのクエリを書くと実現出来そうなんですが･･･
** ※クエリが盛大に間違ってたので修正 **

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

これをMySQL風に書き直そうと思いましたが力尽きました･･･
(MySQLはROW_NUMBER分析関数はサポートしてないのね･･･orz)

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

Answer 4

クッキーに登録した順序で表示しないといけないという業務で使った方法（どんな並びも自由自在）

SELECT * FROM TABLE
ORDER BY
   ( CASE  
     WHEN 条件式(2文字目がA) THEN 1
     WHEN 条件式(2文字目がB) THEN 2
     /* 続く */
     WHEN 条件式(2文字目がZ) THEN 26
     ELSE 999 ) ASC
   

でこれをもと

SELECT ORDER BY
   ( CASE  
     WHEN 条件式(2文字目がA) THEN 1
     WHEN 条件式(2文字目がB) THEN 2
     /* 続く */
     WHEN 条件式(2文字目がZ) THEN 26
     ELSE 999 ) AS ORDER_ITEM. * FROM TABLE
ORDER BY
  ORDER_ITEM ASC

こういうふうにしてごにょごにょすれば出来上がりだけどここからの詰め込みが思いつかないな