MySQLで希望通りに並べ替えるｓｑｌ

Question

mysqlのこの「hoge」テーブルを

id where who
1 osaka ccc
2 tokyo fff
3 osaka bbb
4 tokyo eee
5 osaka aaa
6 tokyo ddd

id doko who
1 tokyo ccc
2 osaka fff
3 tokyo bbb
4 osaka eee
5 tokyo aaa
6 osaka ddd

このように並べたいと思っています。

id where who
1 osaka ccc
3 osaka bbb
5 osaka aaa
2 tokyo fff
4 tokyo eee
6 tokyo ddd

id doko who
1 tokyo ccc
3 tokyo bbb
5 tokyo aaa
2 osaka fff
4 osaka eee
6 osaka ddd

現在は,dokoに最初に出てきた順番に並べるために

where
tokyo
osaka

を抽出して、再度検索しています。

これを1回のｓｑｌで、できないでしょうか。

Answer 1

doko別のidの最小値を、並び順の項目とすれば良さそうです。

select hoge.*
from  hoge inner join (
        select doko, min(id) as sort_id
        from hoge
        group by doko
      ) as sort
      on hoge.doko=sort.doko
order by sort_id, id

Comments

[退会済みユーザー]

おす！

sazi はSQL、超つえぇ奴だったよな。おめぇのSQLでわかんねぇ事があるからちょっと教えてくれ。
それでどうやって、複数あるdokoの並び順を指定したらいいんだ？

あと、初心者相手にタイポはやめとけ。

[退会済みユーザー]

>現在は,dokoに最初に出てきた順番に並べるために

これ固定でいいのか。なら問題ねぇのか。
わりぃわりぃ。

[sazi]

@goku59 さん
タイポ指摘、ありがとうございました。

[d_neko]

別のsqlの質問でもお世話になりました。あの時、本当に聞きたかったのはこの質問です。さて、あの回答を見て思ったのが、「サブクエリを使えば1回でできないかな」です。しかし、「MySQLの場合はサブクエリーが遅い」のですか？これは困ります。これは、その問題がないのでしょうか？

[退会済みユーザー]

>しかし、「MySQLの場合はサブクエリーが遅い」のですか？これは困ります。これは、その問題がないのでしょうか？

それはおめぇ、レコードが何件あるかによるな。あと、MySQLのバージョンにもよる。
古いMySQLは常に１つのindexしか使用しなかったから遅ぇが、
新しいMySQLはそうじゃねぇ。

どういうSQLを書いたら速くなるかは取りたい内容によって変化するので、
「こうやれば正解」って１つだけの解はねぇ。
だから、DB専門のエンジニアが居る。

ただまぁ、数万行程度の話なら考えるだけ無駄で一瞬で結果が返ってくる。

もっとおめぇの求めている条件を出せ。
そうしねぇと、回答者がおめぇのわけのわかんねぇ質問に振り回されて、
だんだん嫌気がさして去っていくだけだから、おめぇのためにならねぇんじゃねぇかと、
おら、思うぞ。

[d_neko]

ありがとうございます。

＞もっとおめぇの求めている条件を出せ。

昔はそうではなかったのですが、
最近、質問すると、質問の内容が、
回答者の都合のいいように変えられたりすることがあります。

回答者の質問に対する質問も、
「それって本当に必要？」ってのもよくあります。
私には、ポイント稼ぎを楽しんでいるようにしか見えないことが多々あります。

今回のように、質問じたい否定されることもあります。
例：そもそもなぜそんな取り出し順にしなきゃいけないのですか？

だから、ここでの質問も、まともな回答されたら
「ラッキー」ぐらいにしか考えていませんでした。

そのため、条件を限りなく削除しています。

[退会済みユーザー]

>今回のように、質問じたい否定されることもあります。
>例：そもそもなぜそんな取り出し順にしなきゃいけないのですか？

ははは、しぶやんの事か。あいつ、頭固ぇからな。
悪気はねぇみてぇなんだが、融通が効かねぇ。

他の奴もそうなんだが、基本的に言われたことしか出来ねぇ。それをするのが仕事だと思ってる。
プログラマなんて、大体そんなもんだ。ロボばっかだからな。

おらは、考えすぎる。
だから、おめぇのタイトルを見た時点で『多分こういう事がしてぇんだろうな』と思って回答した。
その後で sazi の回答見て『へ？　そんだけの事でよかったのか？』って思ったくれぇだ。

結局、前提条件で回答はマイナス１００にもプラス１００００にもなるんだ。

だからな、ロボ相手なんだって理解して、
どういう物がほしいのかをちゃんと説明するのは、
おめぇの責任じゃなくて、おめぇが一番得をする道だと、おら、思うぞ。

Answer 2

おす！

ちょっと、おめぇの希望とは違うかもしんねぇけんどもよぅ、
こんなやり方じゃダメか？

SELECT T.*
FROM
(
	SELECT
		*,
		CASE
			WHEN doko = 'osaka' THEN 1
			WHEN doko = 'tokyo' THEN 2
			WHEN doko = 'aichi' THEN 3
			ELSE 999
		END AS sort_idx
	FROM test.hoge 
) AS T
ORDER BY T.sort_idx ASC;

プログラムで動的にSQL生成していいならUNIONすりゃいいだけなんだが、
SQLだけでやろうと思ったら、
とりあえずおらが思いつくのはこういうやり方だな。

他のやつ用にCREATE TABLEとINSERT置いとくぞ。

CREATE TABLE `test`.`hoge` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `doko` varchar(128) DEFAULT NULL COMMENT '場所',
  `who` varchar(128) DEFAULT NULL COMMENT '誰',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='テストテーブル';

INSERT INTO `test`.`hoge`
(
	`doko`, `who`
)
VALUES
('tokyo', 'ccc'),
('osaka', 'fff'),
('tokyo', 'bbb'),
('osaka', 'eee'),
('tokyo', 'aaa'),
('osaka', 'ddd'),
('aichi', 'zzz');

Comments

[hihijiji]

goku59さの回答が一番近い気がします。
あと ORDER BY 句に T.id を加えてくだされ
もっと細かいこと言うとtokyoが1かと…

[退会済みユーザー]

>もっと細かいこと言うとtokyoが1かと…

そうじゃねぇ。

希望通りに並ぶことをひと目で分かるように、あえてosakaを最初にしてんだ。

でも、質問者が望んでるのは登録されたログでの初出順みてぇだから、sazi のが一番近かったのか？って、おら、思ってるんだけんどもな。

T.id ASC についてはな、『そこはおめぇでも分かるはずだから、おめぇがやれ』って意味で省いた。
全部おらがやっちまったら、質問者の勉強にならねぇからな。

[d_neko]

ありがとうございます。ｓｑｌがよくわかっていませが、これだと、新しいデータが増えるたびに、WHENを増やさなければいけませんか？「プログラムで動的にSQL生成していいならUNION」ってやつだと、簡単に出来るのでしょうか。

[退会済みユーザー]

おす！

>「プログラムで動的にSQL生成していいならUNION」ってやつだと、簡単に出来るのでしょうか。

おお、ものすげぇ簡単だぞ。SQLを動的に生成する事自体はな。
だけんども、そのプログラムを覚えるのがてぇへんだ。

おめぇはどうしてぇ？　それを聞いたら、おらや他のやつが、
きっといいアドバイスできんぞ。

Answer 3

最初に出てきたっていうのは id 順に並べたときのことでしょうか？

SELECT A.* FROM hoge A 
   ORDER BY
       (SELECT MIN(b.id) FROM hoge B WHERE B.doko = A.doko),id

Comments

[sazi]

order by にサブクエリー使えます？

[KOZ6.0]

最新の 8.0.17 だと使えましたよ。
ひょっとして古いとつかえなかったりします？

[sazi]

>ひょっとして古いとつかえなかったりします？
どうなんでしょう。
リファレンスではサブクエリーが使えるような記述には見えないので使ったことは無いです。
MySQLはselectのエイリアスでもorder by の項目として使用できるので、サブクエリー使用するなら、
select で使用する場合が多いとは思いますけど。

[KOZ6.0]

まぁ、d_neko さんの環境で動けばOKかと。
動かなかったらバージョンを確認して報告していただけるとありがたいです。>d_neko さん

[sazi]

質問のサンプル見ると、order by にidが必要な気がします。
サブクエリー版も考えたんですけど、MySQLの場合はサブクエリーが遅いというのもあるので、私はインラインビューでの回答にしました。
d_nekoさんに、この辺の比較も報告してもらえると尚良いと思います。

[KOZ6.0]

sazi さんご指摘ありがとうございました。
id 追加しておきました。

[d_neko]

ありがとうございます。ｓｑｌはよくわかっていませんが、サブクエリを使えばいいんじゃないかなと思っていました。でも、「MySQLの場合はサブクエリーが遅い」ようなので「インラインビュー」ってのが気になっています。実際のデータは、100万件くらいだと思うのですが、そのデータがそろうのは、かなり先になります。今、その違いがテストできないので残念です。

[KOZ6.0]

データについては
INSERT INTO hoge(doko, who) SELECT doko,who FROM hoge
を実行するとデータの数が x2 になるんで、繰り返せばすぐ増やせるかと。

20 万件くらいでやってみたんですが、sazi さんのクエリが数秒で帰ってくるのに対して私のクエリは 30秒タイムアウトしてしまいました。
ここまで違うんですね。

[d_neko]

報告ありがとうございます。まさか、そんなに違いがあるとは思いませんでした。でも、サブクエリって普通の機能じゃないのですか？それがそんなに時間がかかるなんて・・・

[KOZ6.0]

ざっと調べてみたんですが、すべてのサブクエリが遅いというわけではなく、サブクエリで外側のクエリ項目を参照してると遅いようです。(私のクエリでは WHERE B.doko = A.doko のところ)
「MySQL サブクエリ 遅い」でググってみるといろいろ出てくるので参照してみてください。

[KOZ6.0]

これは私の環境(インストールただけのチューニングなし＆ショボPC)での話ですから、ちゃんとしたサーバーだと違う可能性があります。この差が覆るとは思えませんが、d_neko さん自身の手で確認していただければと思います。

Answer 4

カラム名に予約語が入っているのはちょっといただけないですね。

select * from `hoge` ordere by `where` asc

Comments

[d_neko]

もし、osakaとtokyoが逆の場合、希望通りになりません。whereに最初に出てきた順に並べたいとおもっています。

[退会済みユーザー]

なにいってるの？
そもそもなぜそんな取り出し順にしなきゃいけないのですか？

[退会済みユーザー]

おす！

>Kosuke_Shibuya
>なにいってるの？

いや、おめーが何言ってんだ。タイトルよく見てみろ。

>MySQLで「希望通りに」並べ替えるｓｑｌ

だぞ。

[退会済みユーザー]

>そもそもなぜそんな取り出し順にしなきゃいけないのですか？

そういう物が求められてっからだろ。システム開発やったことねぇのか？
プログラマの都合で仕様決まるんじゃねぇぞ。
仕様を満たすようにプログラムを組むんだ。