SQLについて教えてください。使用しているのはMYSQLです。


TABLE1があります。フィールドは、
ID,
NAME(VARCHAR)
BIKO1(INT)
BIKO2(INT)
BIKO3(INT)
です。

・IDには文字列が入っています。長さはバラバラです。
・BIKO1~BIKO3は数値が入っています。
・BIKO1が0または3の時、IDの末尾のみをyに変更。(例:0001がIDに入っていた場合は000yに変換)
・BIKO2が1意外の場合(<>1)、IDの末尾のみをsに変更。(例:0001がIDに入っていた場合は000sに変換)

CASE WHENでわけて、replace関数、len関数あたりを組み合わせれば式が作れそうなかんじがしますが、思いつきません。
どういった式をたてれば上記の条件が実現できるでしょうか?

回答の条件
  • 1人3回まで
  • 13歳以上
  • 登録:2011/04/07 14:02:32
  • 終了:2011/04/12 19:45:58

ベストアンサー

id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542011/04/07 15:07:40

ポイント27pt

MySQLの質問をする場合、MySQL 5.0、5.1といったレベルまで、最低限、バージョンを提示するようにしてください。大きな機能拡張、一部の仕様変更があります。


どちらの条件にも合致する場合、優先度はどうするのでしょうか?



テストのための定義&データ

drop table if exists table1;
create table table1
(seq         int    primary key auto_increment  -- テストの検証のため、追加
,id          varchar(5)
,name        varchar(10)
,biko1       int
,biko2       int
,biko3       int
);

insert into table1(id,biko1,biko2,biko3) values
 ('0001',0,0,null)
,('0002',3,0,null)
,('0003',10,1,null)
,('0004',10,10,null)
--
,('1',0,0,null)
,('2',3,0,null)
,('3',10,1,null)
,('4',10,10,null)
,('11',0,0,null)
,('22',3,0,null)
,('33',10,1,null)
,('44',10,10,null)
;


まずは、更新せずに表示だけ

select
  *
 ,concat(left(id,char_length(id)-1)
        ,case when biko1 in(0,3) then 'y'
              when biko2<>1      then 's'
              else                    right(id,1)
         end
        ) as new_id
 from table1
 order by seq
; 


更新する

update table1
 set id=concat(left(id,char_length(id)-1)
              ,case when biko1 in(0,3) then 'y'
                    when biko2<>1      then 's'
                    else                    right(id,1)
               end
              ) 
 where biko1 in(0,3)
    or biko2<>1
;

母体データ件数は、どのくらいあるのでしょうか?

もし数万件といったレベルなら、検索条件での絞り込みは不要かも知れません。

もしデータ件数が相当に多いなら、インデクスを定義し、検索条件で絞り込むといったことを考えた方がいいでしょう。

id:popattack

ご回答ありがとうございました。MYSQLはwindows用のMYSQL5.5.10です。大変失礼しました。

ご回答頂きました内容で問題無く実行できました。

ただし、chuken_kenkouさんが言われる、「どちらの条件にも合致する場合」もありました。

BIKO1 IN(0,3) の場合と、 BIKO2<>1 の条件が合致してしまった場合は、末尾に'y'を付ける方を優先したい場合、どのように行えばよいでしょうか?

ちなみにデータ数は1万も無い程度です。

2011/04/07 16:50:36

その他の回答(2件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/04/07 14:49:45

ポイント27pt

BIKO1とBIKO2の関係性があいまいなため下記の条件としています

「BIKO1が0または3の時はy、BIKO1が0でも3でもなくBIKO2が1以外はs、それ以外は何もしない」

まずは下記SELECT文で望みどおりか確認してみてください

SELECT ID = CONCAT(LEFT(ID,CHAR_LENGTH(ID) - 1), (CASE WHEN BIKO1 IN (0,3) THEN 'y' ELSE 's' END)
FROM TABLE1
WHERE BIKO1 IN (0,3) OR BIKO2 <> 1

望みどおりならUPDATE文に置き換えて実行

UPDATE TABLE1
SET ID = CONCAT(LEFT(ID,CHAR_LENGTH(ID) - 1), (CASE WHEN BIKO1 IN (0,3) THEN 'y' ELSE 's' END)
WHERE BIKO1 IN (0,3) OR BIKO2 <> 1

望みどおりでない場合はBIKO1とBIKO2の条件を返信欄へ

id:popattack

ご回答ありがとうございました。MYSQLはwindows用のMYSQL5.5.10です。大変失礼しました。

ご回答頂きました内容で問題無く実行できました。

ただし、chuken_kenkouさんが言われる、「どちらの条件にも合致する場合」もありました。

BIKO1 IN(0,3) の場合と、 BIKO2<>1 の条件が合致してしまった場合は、末尾に'y'を付ける方を優先したい場合、どのように行えばよいでしょうか?

ちなみにデータ数は1万も無い程度です。

2011/04/07 16:50:43
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542011/04/07 15:07:40ここでベストアンサー

ポイント27pt

MySQLの質問をする場合、MySQL 5.0、5.1といったレベルまで、最低限、バージョンを提示するようにしてください。大きな機能拡張、一部の仕様変更があります。


どちらの条件にも合致する場合、優先度はどうするのでしょうか?



テストのための定義&データ

drop table if exists table1;
create table table1
(seq         int    primary key auto_increment  -- テストの検証のため、追加
,id          varchar(5)
,name        varchar(10)
,biko1       int
,biko2       int
,biko3       int
);

insert into table1(id,biko1,biko2,biko3) values
 ('0001',0,0,null)
,('0002',3,0,null)
,('0003',10,1,null)
,('0004',10,10,null)
--
,('1',0,0,null)
,('2',3,0,null)
,('3',10,1,null)
,('4',10,10,null)
,('11',0,0,null)
,('22',3,0,null)
,('33',10,1,null)
,('44',10,10,null)
;


まずは、更新せずに表示だけ

select
  *
 ,concat(left(id,char_length(id)-1)
        ,case when biko1 in(0,3) then 'y'
              when biko2<>1      then 's'
              else                    right(id,1)
         end
        ) as new_id
 from table1
 order by seq
; 


更新する

update table1
 set id=concat(left(id,char_length(id)-1)
              ,case when biko1 in(0,3) then 'y'
                    when biko2<>1      then 's'
                    else                    right(id,1)
               end
              ) 
 where biko1 in(0,3)
    or biko2<>1
;

母体データ件数は、どのくらいあるのでしょうか?

もし数万件といったレベルなら、検索条件での絞り込みは不要かも知れません。

もしデータ件数が相当に多いなら、インデクスを定義し、検索条件で絞り込むといったことを考えた方がいいでしょう。

id:popattack

ご回答ありがとうございました。MYSQLはwindows用のMYSQL5.5.10です。大変失礼しました。

ご回答頂きました内容で問題無く実行できました。

ただし、chuken_kenkouさんが言われる、「どちらの条件にも合致する場合」もありました。

BIKO1 IN(0,3) の場合と、 BIKO2<>1 の条件が合致してしまった場合は、末尾に'y'を付ける方を優先したい場合、どのように行えばよいでしょうか?

ちなみにデータ数は1万も無い程度です。

2011/04/07 16:50:36
id:asuka645 No.3

あすか回答回数856ベストアンサー獲得回数972011/04/07 15:40:33

ポイント26pt

下式のように2つに分けてみてください。

もちろん、IDは十分な長さのある文字列型という前提です。

UPDATE TABLE1 SET ID=CONCAT(SUBSTRING(ID, 1, LENGTH(ID) - 1), 'y') WHERE BIKO1=0 OR BIKO1=3;
UPDATE TABLE1 SET ID=CONCAT(SUBSTRING(ID, 1, LENGTH(ID) - 1), 's') WHERE BIKO2<>1;
id:popattack

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

2011/04/07 23:59:12
  • id:popattack
    select文でID,NAME,BIKO1,BIKO2を引っ張ってきたいです。引っ張ってくる時にIDを条件によって加工して表示したいと思っています。
  • id:chuken_kenkou
    主キー(行を一意に識別できる列)は、作ってないのでしょうか?

    実際に更新するのでなく、検索結果の表示だけ変えればいいのでしょうか?

    もし、更新するのであれば、id列が主キーであったり、uniqueである場合、重複するケースはないですか?
  • id:windofjuly
    うぃんど 2011/04/07 15:49:29
    回答1の訂正
    UPDATE文からコピーしたままで訂正忘れてました
    SELECT CONCAT(LEFT(ID,CHAR_LENGTH(ID) - 1), (CASE WHEN BIKO1 IN (0,3) THEN 'y' ELSE 's' END) AS ID, NAME, BIKO1, BIKO2
  • id:windofjuly
    うぃんど 2011/04/07 17:11:09
    >BIKO1 IN(0,3) の場合と、 BIKO2<>1 の条件が合致してしまった場合は、末尾に'y'を付ける方を優先したい

    回答1の冒頭にかきましたとおり、すでに優先してあります
    「BIKO1が0または3の時はy、BIKO1が0でも3でもなくBIKO2が1以外はs、それ以外は何もしない」
    chuken_kenkouさんのご回答も同じくBIKO1が優先されています
     
    以下のような具合になっています(CASEには他の書き方もありますが今回は該当しないので割愛)
    CASE
    WHEN 第一条件 THEN 第一条件が満たされた場合の処理(以後の条件は無視)
    WHEN 第二条件 THEN 第二条件が満たされた場合の処理(以後の条件は無視)
    WHEN 第n条件 THEN 第n条件が満たされた場合の処理(以後の条件は無視)
    ELSE いずれの条件も満たされなかった場合の処理
    END
  • id:popattack
    windofjulyさん
    ご回答ありがとうございます。よく理解できました、ありがとうございます!
  • id:sayo221sayo
    ポイントゲッターが必死すぎて笑えるw
  • id:popattack
    もう1点教えて頂けないでしょうか。下記のSQL文で、IDのlengthが7以下の場合、lpadで0埋めで8桁にしたいと思います。IDが8桁ならそのままIDを載せます。
    9桁以上、12桁以下ならlpadで0埋めで13桁にしたいです。IDが13桁ならそのまま載せます。14桁以上はIDをそのまま載せるというようにしたいと思います。
    IDが1234567の場合、length(ID)=7 ->01234567

    SELECT ID = CONCAT(LEFT(ID,CHAR_LENGTH(ID) - 1), (CASE WHEN BIKO1 IN (0,3) THEN 'y' ELSE 's' END)
    FROM TABLE1
    WHERE BIKO1 IN (0,3) OR BIKO2 <> 1

    よろしくお願いします。
  • id:windofjuly
    うぃんど 2011/04/08 01:25:46
    もう一点どころか別の質問になってるのはスルーしておきます(笑)
     
    いろいろな書き方が出来るから、下のはほんの一例ね
     
    SELECT @a:=CHAR_LENGTH(ID)
    , CASE WHEN @a < 8 THEN @b:=lpad(ID, 8, '0') WHEN @a BETWEEN 9 AND 12 THEN @b:=lpad(ID, 13, '0') ELSE @b:= ID END
    , CONCAT(LEFT(@b,CHAR_LENGTH(@b) - 1), (CASE WHEN BIKO1 IN (0,3) THEN 'y' ELSE 's' END)) AS ID
    , BIKO1, BIKO2
    FROM TABLE1
    WHERE BIKO1 IN (0,3) OR BIKO2 <> 1;
  • id:sayo221sayo
    ポイントにならない投稿はしないと
    流石はポイントゲッター!
  • id:popattack
    windofjulyさん ご回答ありがとうございます。私のレベルではちょっと理解できませんが、勉強します。ありがとうございます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません