studies

いろいろ勉強用備忘録的な感じ

SQLite3でちょっと複雑なソート

studies.hatenablog.com
studies.hatenablog.com

応用情報の勉強をしなければならない。
といっても、なかなか腰が上がらないものである。
気づけば日曜日の夜である。


今回は普段触らないSQLで例の問題を解く。
きっと、応用情報の役に立つはずだ(?)

今、city_tableの中身はこんな感じ。

sqlite> SELECT *
   ...> FROM city_table;
city_id|city_name|pref
1|京都市|京都府
2|千代田区|東京都
3|高岡市|富山県
4|松本市|長野県
5|宮津市|京都府
6|川崎市|神奈川県
7|諏訪市|長野県
8|渋谷区|東京都
9|滑川市|富山県
10|新宿区|東京都
11|臼杵市|大分県
12|稚内市|北海道
13|氷見市|富山県
14|いわき市|福島県
15|長野市|長野県
16|横須賀市|神奈川県
17|宇治市|京都府

これをソートして出力する。

次のようなSQL文でいける。

sqlite> SELECT city_table.*
   ...> FROM city_table
   ...> INNER JOIN ( SELECT pref, MIN(city_id) AS  pref_id
   ...>              FROM city_table
   ...>              GROUP BY pref ) AS pref_table
   ...> ON city_table.pref = pref_table.pref
   ...> ORDER BY pref_table.pref_id, city_table.city_id;
city_id|city_name|pref
1|京都市|京都府
5|宮津市|京都府
17|宇治市|京都府
2|千代田区|東京都
8|渋谷区|東京都
10|新宿区|東京都
3|高岡市|富山県
9|滑川市|富山県
13|氷見市|富山県
4|松本市|長野県
7|諏訪市|長野県
15|長野市|長野県
6|川崎市|神奈川県
16|横須賀市|神奈川県
11|臼杵市|大分県
12|稚内市|北海道
14|いわき市|福島県


まず、サブクエリを使って、pref_tableを作る。
pref_tableは県名と、県の優先度が書かれている。
こんな感じのテーブルだ。

sqlite> SELECT pref, MIN(city_id) AS  pref_id
   ...> FROM city_table
   ...> GROUP BY pref;
pref|pref_id
京都府|1
北海道|12
大分県|11
富山県|3
東京都|2
神奈川県|6
福島県|14
長野県|4

続いて、city_tableとpref_tableを県名をキーにinner join。
pref_idを第一キー、city_idを第二キーにしてorder by。
最後にcity_tableの要素だけを出力。


これでいい…。
と思ったけど、相関サブクエリでスカラを返させたほうがかっこいいかもしれない。

sqlite> SELECT *
   ...> FROM city_table AS XX
   ...> ORDER BY ( SELECT MIN(city_id)
   ...>            FROM city_table AS YY
   ...>            WHERE XX.pref = YY.pref ),
   ...>          city_id;
city_id|city_name|pref
1|京都市|京都府
5|宮津市|京都府
17|宇治市|京都府
2|千代田区|東京都
8|渋谷区|東京都
10|新宿区|東京都
3|高岡市|富山県
9|滑川市|富山県
13|氷見市|富山県
4|松本市|長野県
7|諏訪市|長野県
15|長野市|長野県
6|川崎市|神奈川県
16|横須賀市|神奈川県
11|臼杵市|大分県
12|稚内市|北海道
14|いわき市|福島県


慣れないことをすると頭を使う。