SQLのORDERBY句について質問です。

質問

SQLのORDERBY句について質問です。

データをSQLで並べ替えたいのですが、
AB-125
のような商品名のデータを並べ替えたいと考えています。
しかし、
AB-125
AB-123
AB-20
の様に
10の位でおかしな結果になっています。
対処法のアドバイスよろしくお願いします。

回答No.4

DexMachina · ベストアンサー

データにどのようなパターンがあるかによって、必要になる式が
全く違ってきてしまいますが・・・(汗)

とりあえず、全てが「英字(字数不定)」＋「-」＋「数字(字数不定)」の
パターンであるなら、以下のようなSQLでいけます：
(使用アプリケーションがAccessで、テーブル名が「tbl_商品」、
　並べ替えを行うフィールドの名前が「商品名」としての例)

Select * From tbl_商品
Order by Left([商品名], InStr(1, [商品名], "-")-1), CLng(Mid([商品名], InStr(1, [商品名], "-")+1));

【解説】
まず、InStr関数で「商品名」の中の「-」の位置を割り出します。

これをLeft関数の引数と使用して、「-」の手前まで(→「InStr(～)-1」)
で切り出したのが、Order By句の1条件目の式です。

次に、数値部分も同様に、今度は「-」の次から(→「InStr(～)+1」)の
切り出しになりますが、これにはMid関数を使用します。

但し、Mid関数で切り出しただけでは文字列扱いとなってしまうので、
これをCLng関数を使用して数値化します。
(なお、もしも数字部分が小数もありえるなら、CLng関数の代わりに
　CDbl関数を使用する必要があります)
これがOrder By句の2条件目になります。

※上記のパターンのデータしかないことが前提の式です。
　 このパターンに合わないデータがある(「ABC123」など)場合は、
　 「プロシージャ呼び出しが正しくありません」とのエラーが発生
　 しました(汗)

お礼

非常に参考になりました。
基本的にABC-123形式なのでいける思います。

ありがとうございました。

回答No.3

smileeeen

どのDBを使用されているかにもよりますが、
MySQLであれば下記のようなクエリで
数値として並べ替えができるかと思います。

ただし、「AB-125」のように必ず4文字目から数値が始まる場合に限り、
更に数値の前の文字列は無視されてしまいます＾＾；

SELECT * FROM mytable ORDER BY CAST(SUBSTRING(goods, 4) AS SIGNED);

テーブル名や、カラム名は実装に合わせて変更して下さい。

お礼

アドバイスありがとうございます。
必ず4文字目という訳ではないので、少しリスキーですね。
しかし、参考になりました。
ありがとうございました。

回答No.2

chie65536（@chie65535）

回答欄の文字数が尽きたので、続き。

商品番号を10倍しているのは、前の回答の通り。

商品番号に200000を足しているのは「特別な並びにしたい商品が出てきた時の為」です。

例えば「キャンペーン用の商品を先頭にしたい」とか「廃番商品は必ず末尾にしたい」とかの場合に、頭の「２」を「１」に変えたり「９」に変えたりするのです。

AB-11
AB-45
を廃番にして
AB-123
AB-125
の２つを先頭に持って行きたい場合は、以下のようにソート順を書き換えます。

品名　　ソート順
AB-5　　200050
AB-9　　200090
AB-11(廃)　　900110
AB-22　　200220
AB-45(廃)　　900450
AB-123　　101230
AB-125　　101250

これを「ソート順」でソートすると

品名　　ソート順
AB-123　　101230
AB-125　　101250
AB-5　　200050
AB-9　　200090
AB-22　　200220
AB-11(廃)　　900110
AB-45(廃)　　900450

のようになります。

上記の例では、頭の番号は一桁ですが、２桁でも３桁でも構いません。

うまく付番すれば「百万の桁はこの意味で、この順番。十万の桁はこの意味で、この順番」など、色々と出来ます。

このように、商品番号に200000を足す事で「あとから、前に移動したり、後ろに移動したり」が出来るのです。

下手に1番から使ってしまうと、1番の前には0番しか入れられないので、後で困ります。

お礼

非常に長い説明分ありがとうございました。
大変参考になりました。
桁数を上げて、調整するという考えもあるのですね。

ありがとうございました。

回答No.1

chie65536（@chie65535）

"AB-125"
"AB-123"
"AB-20"
などのデータは「すべて文字列」なので「１文字づつ、文字コード順で大小比較」されます。

大小比較は「最初に異なる１文字」で比較されます。

どれも３文字目までは同一なので、最初の比較は「４文字目のみ」を使って行われます。

"AB-125"
"AB-123"
の２つは「４文字目が"1"」なので「４文字目が"2"」になっている
"AB-20"
よりも小さいと判断されます。

なので「最も大きいのは"AB-20"」です。

"AB-125"
"AB-123"
の２つを比べた場合「５文字目までは同一」なので「６文字目のみ」を使って大小比較します。

「"3"は"5"よりも小さい」ので「"AB-123"の方が小さい」と言う事になります。

結果、文字列として並び替えると
AB-123
AB-125
AB-20
の順番になります。

データベースで「商品コード」などのような「英字と数字を組み合わせたコード」を付番してソートしたい場合は「数字部分の文字位置を右詰めにする」と言う配慮が必要です。

例えば
「ＡＢタイプの5番」⇒「AB-005」
「ＡＢタイプの9番」⇒「AB-009」
「ＡＢタイプの11番」⇒「AB-011」
「ＡＢタイプの22番」⇒「AB-022」
「ＡＢタイプの45番」⇒「AB-045」
「ＡＢタイプの123番」⇒「AB-123」
「ＡＢタイプの125番」⇒「AB-125」
などのように付番しなければなりません。

これを
「ＡＢタイプの5番」⇒「AB-5」
「ＡＢタイプの9番」⇒「AB-9」
「ＡＢタイプの11番」⇒「AB-11」
「ＡＢタイプの22番」⇒「AB-22」
「ＡＢタイプの45番」⇒「AB-45」
「ＡＢタイプの123番」⇒「AB-123」
「ＡＢタイプの125番」⇒「AB-125」
と付番すると、ソートした時に
「ＡＢタイプの11番」⇒「AB-11」
「ＡＢタイプの123番」⇒「AB-123」
「ＡＢタイプの125番」⇒「AB-125」
「ＡＢタイプの22番」⇒「AB-22」
「ＡＢタイプの45番」⇒「AB-45」
「ＡＢタイプの5番」⇒「AB-5」
「ＡＢタイプの9番」⇒「AB-9」
という順番になってしまいます。

「名前やコードを今更変えられない」と言う場合は、新たな「ソート用の順番テーブル」を作成して対処します。

例えば、ソート用テーブルに「品名(文字列型)」と「ソート順(長整数型)」と言う項目を持たせ
品名　　ソート順
AB-5　　200050
AB-9　　200090
AB-11　　200110
AB-22　　200220
AB-45　　200450
AB-123　　201230
AB-125　　201250
と言うテーブルを作っておきます。

そして、このテーブルをJOIN句で連結し「ソート順」をORDER BY句に指定しましょう。

なお、ソート順が「商品番号を10倍して200000を足した数」になっているのには理由があります。

もし
AB-9
AB-10
の間に
AB-9S
を増やして
AB-9
AB-9S
AB-10
にしたい場合があったら？

仮に「ソート順」が
AB-9　　20009
AB-10　　20010
だったら「20009」と「20010」の間に来る数値を入れられません（項目は長整数型ですから小数点は入りません）

なので、10倍した番号にしておいて
AB-9　　200090
AB-9S　　200095
AB-10　　200100
のように、間に番号を増やすのです。

当然ですが
AB-9　　200090
AB-9S1　　200091
AB-9S2　　200092
AB-10　　200100
と言う増やし方は、やってはいけません。

続き番号で間に増やすと、以下のように、間に新商品を増やせません。
AB-9　　200090
AB-9N1　　番号に間があいてないので増やせない
AB-9S1　　200091
AB-9S2　　200092
AB-10　　200100

後ろの枝番と違ってしまい、ちょっと違和感があるかも知れませんが、必ず間をあけて
AB-9　　200090
AB-9N1　　200092
AB-9S1　　200095
AB-9S2　　200097
AB-10　　200100
のように追加していきましょう。