SQL見るだけクエリ#017 ランキングに順位番号を取得する簡単な方法（FIND_IN_SET）

SQL見るだけクエリ。今回は、FIND_IN_SETを使って、順位番号をつけるSQLを書いてみようと思います。

この記事を読むと（約5分）
SQLを使って特定の項目に対して、全体の中で何番目の数値に値するのか、ランキングをふっていく方法がわかります。

MYSQLのデータベースを利用する場合、FIND_IN_SETという関数が使えます。
FIND_IN_SET 関数は、指定した文字列が、カンマ区切りの文字列のうちのどこにあるかを調べる関数です。

例えば、次のようなSQL文を実行した場合、
SELECT FIND_IN_SET('a', 'a,b,c,d');
結果は、「1」となります。これは、検索キーワードが「a」であり、
カンマ区切りの文字列「a,b,c,d」のうちの1番目にあるからです。

また今回、'a,b,c,d'にあたる部分、データをカンマ区切りで格納する部分を、
GROUP_CONCATを使って作成します。

GROUP_CONCAT関数は、MySQLのデータベース管理システムにおいて、
グループ化された結果をカンマで区切って1つの文字列に結合する関数です。

例えば、次のようなSQL文を実行した場合、
SELECT GROUP_CONCAT(name) FROM users;
結果は、「tanaka, sato, takahashi」のような形で表示されます。

具体的には、以下のような、テーブルを準備しました。
各空港ごとの、利用者数のデータです。データは国際線・国内線を含めた総利用者数の多い空港順に並んでいます。
ここで、国内線のみでみた際の、利用者数の順位をSQLで取得して表示してみようと思います。

以下の様なテーブルを用意しました。（テーブル名：sql_airport）

                id airport_code airport_name pref_code pref_name passengers_sum domestic_sum international_sum
1 22827 羽田空港(東京) 13 東京都 86920293 68382811 18537482
2 29110 成田空港(東京) 12 千葉県 42413928 7642779 34771149
3 25892 関西空港(大阪) 27 大阪府 31807820 6981770 24826050
4 29655 福岡空港 40 福岡県 24679617 18281552 6398065
5 29654 新千歳空港(札幌) 1 北海道 24599263 20732744 3866519
6 29657 那覇空港(沖縄) 47 沖縄県 21761828 18080998 3680830
7 25856 伊丹空港(大阪) 27 大阪府 16504209 16504209 0
8 29411 中部国際空港(名古屋) 23 愛知県 13460149 6676623 6783526
9 28935 鹿児島空港 46 鹿児島県 6075210 5663539 411671
10 21045 仙台空港 4 宮城県 3855387 3462344 393043
11 28628 熊本空港 43 熊本県 3492188 3321313 170875
12 29656 宮崎空港 45 宮崎県 3410361 3312416 97945
13 29150 神戸空港 28 兵庫県 3362720 3362671 49
14 28535 長崎空港 42 長崎県 3360170 3275270 84900
15 27367 広島空港 34 広島県 3166572 2821076 345496
16 27893 松山空港 38 愛媛県 3152419 3054961 97458
17 29116 石垣空港 47 沖縄県 2614822 2515210 99612
18 27725 高松空港 37 香川県 2152430 1815458 336972
19 28743 大分空港 44 大分県 1982377 1876887 105490
20 23691 小松空港 17 石川県 1887535 1653086 234449
21 29115 宮古空港 47 沖縄県 1803490 1803490 0
22 20443 函館空港 1 北海道 1800577 1632697 167880
23 28133 北九州空港 40 福岡県 1753525 1450606 302919
24 27970 高知空港 39 高知県 1656279 1653971 2308
25 27053 岡山空港 33 岡山県 1611958 1312970 298988
26 21138 秋田空港 5 秋田県 1380863 1369033 11830
27 20620 青森空港 2 青森県 1250569 1178205 72364
28 27649 徳島空港 36 徳島県 1218852 1210359 8493
29 24034 新潟空港 15 新潟県 1201419 1063706 137713
30 20013 旭川空港 1 北海道 1158948 1109517 49431
31 26923 出雲空港 32 島根県 1051155 1049260 1895
32 27584 山口宇部空港 35 山口県 1028166 1014939 13227
33 25079 名古屋空港 23 愛知県 942753 942062 691
34 29114 奄美空港 46 鹿児島県 891990 891990 0
35 20178 釧路空港 1 北海道 866970 866198 772
36 20570 女満別空港 1 北海道 860458 858500 1958
37 29445 茨城空港 8 茨城県 822208 653265 168943
38 29152 佐賀空港 41 佐賀県 813082 610419 202663
39 29148 静岡空港 22 静岡県 805195 488053 317142
40 20106 帯広空港 1 北海道 701557 700943 614
41 27033 米子空港 31 鳥取県 692137 604792 87345
42 24723 富山空港 16 富山県 575172 455614 119558
43 20908 花巻空港 3 岩手県 516742 470076 46666
44 29706 岩国空港 35 山口県 513750 513750 0
45 21335 庄内空港 6 山形県 442566 437072 5494
46 26881 鳥取空港 31 鳥取県 417740 410892 6848
47 21393 山形空港 6 山形県 366678 338653 28025
48 20772 三沢空港 2 青森県 309527 309527 0
49 21552 福島空港 7 福島県 282437 255617 26820
50 20081 丘珠空港(札幌) 1 北海道 277425 277425 0
51 29126 久米島空港 47 沖縄県 264524 264524 0
52 29119 対馬空港 42 長崎県 257687 257687 0
53 20428 根室中標津空港 1 北海道 214875 214875 0
54 29113 八丈島空港 13 東京都 213569 213569 0
55 29123 徳之島空港 46 鹿児島県 207542 207542 0
56 20613 稚内空港 1 北海道 203654 203654 0
57 26812 南紀白浜空港 30 和歌山県 182158 182158 0
58 29153 能登空港 17 石川県 174544 172929 1615
59 29118 五島福江空港 42 長崎県 168974 168974 0
60 29147 大館能代空港 5 秋田県 159214 159214 0
61 24326 松本空港 20 長野県 155898 153676 2222
62 26938 萩・石見空港 32 島根県 152725 152725 0
63 29122 屋久島空港 46 鹿児島県 145973 145973 0
64 29124 沖永良部空港 46 鹿児島県 119636 119636 0
65 30011 下地島空港 47 沖縄県 109246 92000 17246
66 29127 与那国空港 47 沖縄県 104463 104463 0
67 29141 調布飛行場 13 東京都 97041 97041 0
68 29128 喜界空港 46 鹿児島県 89729 89729 0
69 29121 種子島空港 46 鹿児島県 88513 88513 0
70 29125 与論空港 46 鹿児島県 78227 78227 0
71 20107 オホーツク紋別空港 1 北海道 77299 77299 0
72 29117 隠岐空港 32 島根県 63732 63732 0
73 29135 南大東空港 47 沖縄県 47871 47871 0
74 29129 利尻空港 1 北海道 45183 45183 0
75 29136 多良間空港 47 沖縄県 44809 44809 0
76 29313 天草空港 43 熊本県 43982 43982 0
77 26436 但馬空港 28 兵庫県 42105 42105 0
78 29120 壱岐空港 42 長崎県 34992 34992 0
79 29142 新島空港 13 東京都 32343 32343 0
80 29112 三宅島空港 13 東京都 29163 29163 0
81 29111 大島空港 13 東京都 24539 24539 0
82 29143 神津島空港 13 東京都 22638 22638 0
83 29134 北大東空港 47 沖縄県 21651 21651 0
84 29131 奥尻空港 1 北海道 11881 11881 0
85 30009 粟国空港 47 沖縄県 850 850 0
86 30010 慶良間空港 47 沖縄県 193 193 0
87 30005 小値賀空港 42 長崎県 5 5 0
88 30007 岡南飛行場 33 岡山県 2 2 0
89 30001 礼文空港 1 北海道 0 0 0
90 30002 佐渡空港 15 新潟県 0 0 0
91 30003 福井空港 18 福井県 0 0 0
92 30004 八尾空港 27 大阪府 0 0 0
93 30006 上五島空港 42 長崎県 0 0 0
94 30008 大分県央飛行場 44 大分県 0 0 0
95 30012 伊江島空港 47 沖縄県 0 0 0
96 30013 波照間空港 47 沖縄県 0 0 0

id	airport_code	airport_name	pref_code	pref_name	passengers_sum	domestic_sum	international_sum
1	22827	羽田空港(東京)	13	東京都	86920293	68382811	18537482
2	29110	成田空港(東京)	12	千葉県	42413928	7642779	34771149
3	25892	関西空港(大阪)	27	大阪府	31807820	6981770	24826050
4	29655	福岡空港	40	福岡県	24679617	18281552	6398065
5	29654	新千歳空港(札幌)	1	北海道	24599263	20732744	3866519
6	29657	那覇空港(沖縄)	47	沖縄県	21761828	18080998	3680830
7	25856	伊丹空港(大阪)	27	大阪府	16504209	16504209	0
8	29411	中部国際空港(名古屋)	23	愛知県	13460149	6676623	6783526
9	28935	鹿児島空港	46	鹿児島県	6075210	5663539	411671
10	21045	仙台空港	4	宮城県	3855387	3462344	393043
11	28628	熊本空港	43	熊本県	3492188	3321313	170875
12	29656	宮崎空港	45	宮崎県	3410361	3312416	97945
13	29150	神戸空港	28	兵庫県	3362720	3362671	49
14	28535	長崎空港	42	長崎県	3360170	3275270	84900
15	27367	広島空港	34	広島県	3166572	2821076	345496
16	27893	松山空港	38	愛媛県	3152419	3054961	97458
17	29116	石垣空港	47	沖縄県	2614822	2515210	99612
18	27725	高松空港	37	香川県	2152430	1815458	336972
19	28743	大分空港	44	大分県	1982377	1876887	105490
20	23691	小松空港	17	石川県	1887535	1653086	234449
21	29115	宮古空港	47	沖縄県	1803490	1803490	0
22	20443	函館空港	1	北海道	1800577	1632697	167880
23	28133	北九州空港	40	福岡県	1753525	1450606	302919
24	27970	高知空港	39	高知県	1656279	1653971	2308
25	27053	岡山空港	33	岡山県	1611958	1312970	298988
26	21138	秋田空港	5	秋田県	1380863	1369033	11830
27	20620	青森空港	2	青森県	1250569	1178205	72364
28	27649	徳島空港	36	徳島県	1218852	1210359	8493
29	24034	新潟空港	15	新潟県	1201419	1063706	137713
30	20013	旭川空港	1	北海道	1158948	1109517	49431
31	26923	出雲空港	32	島根県	1051155	1049260	1895
32	27584	山口宇部空港	35	山口県	1028166	1014939	13227
33	25079	名古屋空港	23	愛知県	942753	942062	691
34	29114	奄美空港	46	鹿児島県	891990	891990	0
35	20178	釧路空港	1	北海道	866970	866198	772
36	20570	女満別空港	1	北海道	860458	858500	1958
37	29445	茨城空港	8	茨城県	822208	653265	168943
38	29152	佐賀空港	41	佐賀県	813082	610419	202663
39	29148	静岡空港	22	静岡県	805195	488053	317142
40	20106	帯広空港	1	北海道	701557	700943	614
41	27033	米子空港	31	鳥取県	692137	604792	87345
42	24723	富山空港	16	富山県	575172	455614	119558
43	20908	花巻空港	3	岩手県	516742	470076	46666
44	29706	岩国空港	35	山口県	513750	513750	0
45	21335	庄内空港	6	山形県	442566	437072	5494
46	26881	鳥取空港	31	鳥取県	417740	410892	6848
47	21393	山形空港	6	山形県	366678	338653	28025
48	20772	三沢空港	2	青森県	309527	309527	0
49	21552	福島空港	7	福島県	282437	255617	26820
50	20081	丘珠空港(札幌)	1	北海道	277425	277425	0
51	29126	久米島空港	47	沖縄県	264524	264524	0
52	29119	対馬空港	42	長崎県	257687	257687	0
53	20428	根室中標津空港	1	北海道	214875	214875	0
54	29113	八丈島空港	13	東京都	213569	213569	0
55	29123	徳之島空港	46	鹿児島県	207542	207542	0
56	20613	稚内空港	1	北海道	203654	203654	0
57	26812	南紀白浜空港	30	和歌山県	182158	182158	0
58	29153	能登空港	17	石川県	174544	172929	1615
59	29118	五島福江空港	42	長崎県	168974	168974	0
60	29147	大館能代空港	5	秋田県	159214	159214	0
61	24326	松本空港	20	長野県	155898	153676	2222
62	26938	萩・石見空港	32	島根県	152725	152725	0
63	29122	屋久島空港	46	鹿児島県	145973	145973	0
64	29124	沖永良部空港	46	鹿児島県	119636	119636	0
65	30011	下地島空港	47	沖縄県	109246	92000	17246
66	29127	与那国空港	47	沖縄県	104463	104463	0
67	29141	調布飛行場	13	東京都	97041	97041	0
68	29128	喜界空港	46	鹿児島県	89729	89729	0
69	29121	種子島空港	46	鹿児島県	88513	88513	0
70	29125	与論空港	46	鹿児島県	78227	78227	0
71	20107	オホーツク紋別空港	1	北海道	77299	77299	0
72	29117	隠岐空港	32	島根県	63732	63732	0
73	29135	南大東空港	47	沖縄県	47871	47871	0
74	29129	利尻空港	1	北海道	45183	45183	0
75	29136	多良間空港	47	沖縄県	44809	44809	0
76	29313	天草空港	43	熊本県	43982	43982	0
77	26436	但馬空港	28	兵庫県	42105	42105	0
78	29120	壱岐空港	42	長崎県	34992	34992	0
79	29142	新島空港	13	東京都	32343	32343	0
80	29112	三宅島空港	13	東京都	29163	29163	0
81	29111	大島空港	13	東京都	24539	24539	0
82	29143	神津島空港	13	東京都	22638	22638	0
83	29134	北大東空港	47	沖縄県	21651	21651	0
84	29131	奥尻空港	1	北海道	11881	11881	0
85	30009	粟国空港	47	沖縄県	850	850	0
86	30010	慶良間空港	47	沖縄県	193	193	0
87	30005	小値賀空港	42	長崎県	5	5	0
88	30007	岡南飛行場	33	岡山県	2	2	0
89	30001	礼文空港	1	北海道	0	0	0
90	30002	佐渡空港	15	新潟県	0	0	0
91	30003	福井空港	18	福井県	0	0	0
92	30004	八尾空港	27	大阪府	0	0	0
93	30006	上五島空港	42	長崎県	0	0	0
94	30008	大分県央飛行場	44	大分県	0	0	0
95	30012	伊江島空港	47	沖縄県	0	0	0
96	30013	波照間空港	47	沖縄県	0	0	0

テーブルの情報を取得するSQLに関して、注目する部分は、以下の9~14行目のみです。

SELECT id,airport_name,pref_name,passengers_sum,domestic_sum,FIND_IN_SET(domestic_sum,(SELECT GROUP_CONCAT(domestic_sum ORDER BY domestic_sum DESC) FROM sql_airport)) AS rank FROM sql_airport　この部分で、データベース内にあるtable_nameテーブルの、国内線利用者数の項目（domestic_sum）を、全件取得し数の多い順に並び替えながら、カンマ区切りの文字列を作成します。対象となる空港の国内線利用者の値が、カンマ区切りの文字列の何番目に出現するかを読み取りランキング（rank）として値を代入しております。

try {
    include_once '../model/class.php';

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする
    $query = 'SELECT id,airport_name,pref_name,passengers_sum,domestic_sum,
    FIND_IN_SET(domestic_sum, 
    (SELECT GROUP_CONCAT(
    domestic_sum ORDER BY domestic_sum DESC
    ) FROM sql_airport)) AS rank 
    FROM sql_airport';
    $stmt = $db->prepare($query);

    // 実行する
    $stmt->execute();

    // 結果を取得する
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    // データベースから切断する
    $database->disconnect();

} catch(PDOException $e) {
    echo $e->getMessage();
}

これを実行すると、二次元連想配列という形式で、情報を取得することができます。

見やすくする為に、HTMLとCSSで体裁を整えると、以下のようになります。

空港別利用者数
（赤文字は国内線利用者順位）

総順位	空港名	都道府県名	総利用者数	国内線利用者数	国内線順位
1	羽田空港(東京)	東京都	86920293	68382811	1
2	成田空港(東京)	千葉県	42413928	7642779	6
3	関西空港(大阪)	大阪府	31807820	6981770	7
4	福岡空港	福岡県	24679617	18281552	3
5	新千歳空港(札幌)	北海道	24599263	20732744	2
6	那覇空港(沖縄)	沖縄県	21761828	18080998	4
7	伊丹空港(大阪)	大阪府	16504209	16504209	5
8	中部国際空港(名古屋)	愛知県	13460149	6676623	8
9	鹿児島空港	鹿児島県	6075210	5663539	9
10	仙台空港	宮城県	3855387	3462344	10
11	熊本空港	熊本県	3492188	3321313	12
12	宮崎空港	宮崎県	3410361	3312416	13
13	神戸空港	兵庫県	3362720	3362671	11
14	長崎空港	長崎県	3360170	3275270	14
15	広島空港	広島県	3166572	2821076	16
16	松山空港	愛媛県	3152419	3054961	15
17	石垣空港	沖縄県	2614822	2515210	17
18	高松空港	香川県	2152430	1815458	19
19	大分空港	大分県	1982377	1876887	18
20	小松空港	石川県	1887535	1653086	22
21	宮古空港	沖縄県	1803490	1803490	20
22	函館空港	北海道	1800577	1632697	23
23	北九州空港	福岡県	1753525	1450606	24
24	高知空港	高知県	1656279	1653971	21
25	岡山空港	岡山県	1611958	1312970	26
26	秋田空港	秋田県	1380863	1369033	25
27	青森空港	青森県	1250569	1178205	28
28	徳島空港	徳島県	1218852	1210359	27
29	新潟空港	新潟県	1201419	1063706	30
30	旭川空港	北海道	1158948	1109517	29
31	出雲空港	島根県	1051155	1049260	31
32	山口宇部空港	山口県	1028166	1014939	32
33	名古屋空港	愛知県	942753	942062	33
34	奄美空港	鹿児島県	891990	891990	34
35	釧路空港	北海道	866970	866198	35
36	女満別空港	北海道	860458	858500	36
37	茨城空港	茨城県	822208	653265	38
38	佐賀空港	佐賀県	813082	610419	39
39	静岡空港	静岡県	805195	488053	42
40	帯広空港	北海道	701557	700943	37
41	米子空港	鳥取県	692137	604792	40
42	富山空港	富山県	575172	455614	44
43	花巻空港	岩手県	516742	470076	43
44	岩国空港	山口県	513750	513750	41
45	庄内空港	山形県	442566	437072	45
46	鳥取空港	鳥取県	417740	410892	46
47	山形空港	山形県	366678	338653	47
48	三沢空港	青森県	309527	309527	48
49	福島空港	福島県	282437	255617	52
50	丘珠空港(札幌)	北海道	277425	277425	49
51	久米島空港	沖縄県	264524	264524	50
52	対馬空港	長崎県	257687	257687	51
53	根室中標津空港	北海道	214875	214875	53
54	八丈島空港	東京都	213569	213569	54
55	徳之島空港	鹿児島県	207542	207542	55
56	稚内空港	北海道	203654	203654	56
57	南紀白浜空港	和歌山県	182158	182158	57
58	能登空港	石川県	174544	172929	58
59	五島福江空港	長崎県	168974	168974	59
60	大館能代空港	秋田県	159214	159214	60
61	松本空港	長野県	155898	153676	61
62	萩・石見空港	島根県	152725	152725	62
63	屋久島空港	鹿児島県	145973	145973	63
64	沖永良部空港	鹿児島県	119636	119636	64
65	下地島空港	沖縄県	109246	92000	67
66	与那国空港	沖縄県	104463	104463	65
67	調布飛行場	東京都	97041	97041	66
68	喜界空港	鹿児島県	89729	89729	68
69	種子島空港	鹿児島県	88513	88513	69
70	与論空港	鹿児島県	78227	78227	70
71	オホーツク紋別空港	北海道	77299	77299	71
72	隠岐空港	島根県	63732	63732	72
73	南大東空港	沖縄県	47871	47871	73
74	利尻空港	北海道	45183	45183	74
75	多良間空港	沖縄県	44809	44809	75
76	天草空港	熊本県	43982	43982	76
77	但馬空港	兵庫県	42105	42105	77
78	壱岐空港	長崎県	34992	34992	78
79	新島空港	東京都	32343	32343	79
80	三宅島空港	東京都	29163	29163	80
81	大島空港	東京都	24539	24539	81
82	神津島空港	東京都	22638	22638	82
83	北大東空港	沖縄県	21651	21651	83
84	奥尻空港	北海道	11881	11881	84
85	粟国空港	沖縄県	850	850	85
86	慶良間空港	沖縄県	193	193	86
87	小値賀空港	長崎県	5	5	87
88	岡南飛行場	岡山県	2	2	88
89	礼文空港	北海道	0	0	89
90	佐渡空港	新潟県	0	0	89
91	福井空港	福井県	0	0	89
92	八尾空港	大阪府	0	0	89
93	上五島空港	長崎県	0	0	89
94	大分県央飛行場	大分県	0	0	89
95	伊江島空港	沖縄県	0	0	89
96	波照間空港	沖縄県	0	0	89

SQL見るだけクエリ#017 ランキングに順位番号を取得する簡単な方法（FIND_IN_SET）

空港別利用者数（赤文字は国内線利用者順位）

空港別利用者数
（赤文字は国内線利用者順位）