質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.11%

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

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 1,103

balo0416

score 22

前提・実現したいこと

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では難しいでしょうか。。
宜しくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • jm1156

    2016/08/04 19:14

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

    キャンセル

  • balo0416

    2016/08/05 11:02

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

    キャンセル

回答 4

+6

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句がないと出力順が保証されないのは有名ですね。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/22 12:42

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

    キャンセル

0

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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から循環し直す仕様にしてます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

-4

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/08/04 20:07 編集

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

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

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

    キャンセル

  • 2016/08/05 11:08

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

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

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

    キャンセル

  • 2018/09/18 07:23

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.11%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる