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

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

ただいまの
回答率

90.03%

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

解決済

回答 5

投稿 編集

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

balo0416

score 22

前提

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()

追記

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

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

宜しくお願い致します。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • yambejp

    2016/08/08 18:20

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

    キャンセル

  • balo0416

    2016/08/08 18:24

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

    キャンセル

回答 5

+6

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句は、後回しに評価した場合ですね。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

+1

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

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(^-^;


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

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/08 18:52

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

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

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

    キャンセル

  • 2016/08/08 19:27

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

    キャンセル

  • 2016/08/08 20:04

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

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

    キャンセル

  • 2016/08/09 17:34

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

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

    キャンセル

0

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

ただサンプルにあげてるデータだけを見ると、

  1.  IDが切り変わるタイミングで採番をリセットする
  2. さらに言うと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から循環し直す仕様にしてます。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/09 17:35

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

    キャンセル

0

たとえばこんな感じでどうでしょう?
ユーザー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.枝番;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/09 17:38

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

    キャンセル

checkベストアンサー

-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 であまりを計算)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/09 17:29

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

    キャンセル

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

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