閃乱カグラのデータで勉強するSQL 3 -- 自己結合・自己相関サブクエリ
りんごとかバナナとかで練習しても一つも面白くないので、
閃乱カグラのキャラクターのスリーサイズ等、現実的なデータで練習をしていく
- 非順序対をつくる -- 勢力ごとに、学年が異なる者でペアを組む
- 部分的に不一致なデータの検索 -- バストとヒップが一致するがウエストが一致しないデータを探す
- 飛び石ランキングを作る -- バストサイズで
- 飛び石でないランキングを作る
- 参考
非順序対をつくる -- 勢力ごとに、学年が異なる者でペアを組む
-- 勢力ごとに、学年が異なる者でペアを組む SELECT K1.team, K1.name AS name1, K2.name AS name2 FROM kagura K1 INNER JOIN kagura K2 ON COALESCE(K1.grade, K2.grade) IS NOT NULL -- 学年が定義されているキャラのみ AND K1.team = K2.team -- 同じ勢力 AND K1.grade <> k2.grade -- 異なる学年 AND K1.name < K2.name; -- 重複を許さない非順序対
team | name1 | name2 |
---|---|---|
月閃 | 叢 | 四季 |
月閃 | 叢 | 夜桜 |
月閃 | 叢 | 美野里 |
月閃 | 夜桜 | 美野里 |
月閃 | 夜桜 | 雪泉 |
月閃 | 四季 | 夜桜 |
月閃 | 四季 | 雪泉 |
月閃 | 美野里 | 雪泉 |
半蔵 | 斑鳩 | 柳生 |
半蔵 | 斑鳩 | 雲雀 |
半蔵 | 斑鳩 | 飛鳥 |
半蔵 | 葛城 | 雲雀 |
半蔵 | 葛城 | 飛鳥 |
半蔵 | 柳生 | 葛城 |
半蔵 | 柳生 | 飛鳥 |
半蔵 | 雲雀 | 飛鳥 |
蛇女 | 紫 | 雅緋 |
蛇女 | 忌夢 | 紫 |
蛇女 | 両備 | 忌夢 |
蛇女 | 両備 | 紫 |
蛇女 | 両備 | 雅緋 |
蛇女 | 両備(転身後) | 忌夢 |
蛇女 | 両備(転身後) | 紫 |
蛇女 | 両備(転身後) | 雅緋 |
蛇女 | 両奈 | 忌夢 |
蛇女 | 両奈 | 紫 |
蛇女 | 両奈 | 雅緋 |
紅蓮 | 日影 | 未来 |
紅蓮 | 日影 | 焔 |
紅蓮 | 日影 | 詠 |
紅蓮 | 未来 | 焔 |
紅蓮 | 未来 | 詠 |
紅蓮 | 春花 | 未来 |
紅蓮 | 春花 | 焔 |
紅蓮 | 春花 | 詠 |
部分的に不一致なデータの検索 -- バストとヒップが一致するがウエストが一致しないデータを探す
-- バストとヒップが一致するがウエストが一致しないデータを探す -- 3人が該当する SELECT DISTINCT -- 必要。ないと、3P2 = 6行のレコードが出てきてしまう K1.name, K1.boob, K1.waist, K1.hip FROM kagura K1 INNER JOIN kagura K2 ON K1.boob = K2.boob -- 同じバスト AND K1.hip = k2.hip -- 同じヒップ AND K1.waist <> k2.waist; -- 異なるウエスト
name | boob | waist | hip |
---|---|---|---|
両備(転身後) | 95 | 56 | 90 |
葛城 | 95 | 57 | 90 |
詠 | 95 | 58 | 90 |
両備ちゃんと詠ちゃんは知ってたけど、葛城もだったんですね
飛び石ランキングを作る -- バストサイズで
1位が2人いたら次は3位、という感じのランキング
自己相関サブクエリ方式
- 1位:バストがさらに大きな者は0人
- 2位:バストがさらに大きな者は1人
- and so on so forth
SELECT K1.name, K1.boob, -- カーソル対象よりもバストの大きなキャラを数える (SELECT COUNT(K2.boob) FROM kagura K2 WHERE K1.boob < K2.boob) + 1 AS rank -- 順位を1から数えるために1足している FROM kagura K1 ORDER BY rank;
name | boob | rank |
---|---|---|
紫 | 105 | 1 |
大道寺先輩 | 100 | 2 |
春花 | 99 | 3 |
両奈 | 98 | 4 |
凛 | 97 | 5 |
叢 | 96 | 6 |
華毘 | 96 | 6 |
四季 | 95 | 8 |
葛城 | 95 | 8 |
両備(転身後) | 95 | 8 |
詠 | 95 | 8 |
神楽 | 95 | 8 |
両姫 | 94 | 13 |
斑鳩 | 93 | 14 |
蓮華 | 93 | 14 |
雪泉 | 92 | 16 |
奈楽 | 92 | 16 |
夜桜 | 90 | 18 |
飛鳥 | 90 | 18 |
雅緋 | 90 | 18 |
忌夢 | 88 | 21 |
焔 | 87 | 22 |
美野里 | 86 | 23 |
柳生 | 85 | 24 |
日影 | 85 | 24 |
雲雀 | 80 | 26 |
華風流 | 73 | 27 |
両備 | 69 | 28 |
未来 | 62 | 29 |
自己結合方式 -- 集合指向的
- 1位:バストがさらに大きな者の集合は
φ
- 2位:バストがさらに大きな者の集合は
{1位の者}
- and so on so forth
集約前
-- バストサイズランキング -- 自己結合方式 -- 過渡 SELECT K1.name, K2.name AS name_of_larger_boob, K1.boob FROM kagura K1 -- 「よりバストの大きな者0人」も数えるため左外部結合する -- INNER JOINにしてしまうと、1位が消えてしまう LEFT OUTER JOIN kagura K2 ON k1.boob < k2.boob ORDER BY K1.boob DESC, K2.boob DESC;
巨大なので一部のみ
name | name_of_larger_boob | boob |
---|---|---|
紫 | 105 | |
大道寺先輩 | 紫 | 100 |
春花 | 紫 | 99 |
春花 | 大道寺先輩 | 99 |
両奈 | 紫 | 98 |
両奈 | 大道寺先輩 | 98 |
両奈 | 春花 | 98 |
凛 | 紫 | 97 |
凛 | 大道寺先輩 | 97 |
凛 | 春花 | 97 |
凛 | 両奈 | 97 |
叢 | 紫 | 96 |
華毘 | 紫 | 96 |
叢 | 大道寺先輩 | 96 |
華毘 | 大道寺先輩 | 96 |
叢 | 春花 | 96 |
華毘 | 春花 | 96 |
叢 | 両奈 | 96 |
華毘 | 両奈 | 96 |
叢 | 凛 | 96 |
集約後
-- バストサイズランキング -- 自己結合方式 SELECT K1.name, K1.boob, COUNT(K2.boob) + 1 as rank FROM kagura K1 -- 「よりバストの大きな者0人」も数えるため左外部結合する -- INNER JOINにしてしまうと、1位が消えてしまう LEFT OUTER JOIN kagura K2 ON k1.boob < k2.boob GROUP BY K1.name ORDER BY rank;
name | boob | rank |
---|---|---|
紫 | 105 | 1 |
大道寺先輩 | 100 | 2 |
春花 | 99 | 3 |
両奈 | 98 | 4 |
凛 | 97 | 5 |
叢 | 96 | 6 |
華毘 | 96 | 6 |
両備(転身後) | 95 | 8 |
四季 | 95 | 8 |
神楽 | 95 | 8 |
葛城 | 95 | 8 |
詠 | 95 | 8 |
両姫 | 94 | 13 |
斑鳩 | 93 | 14 |
蓮華 | 93 | 14 |
奈楽 | 92 | 16 |
雪泉 | 92 | 16 |
夜桜 | 90 | 18 |
雅緋 | 90 | 18 |
飛鳥 | 90 | 18 |
忌夢 | 88 | 21 |
焔 | 87 | 22 |
美野里 | 86 | 23 |
日影 | 85 | 24 |
柳生 | 85 | 24 |
雲雀 | 80 | 26 |
華風流 | 73 | 27 |
両備 | 69 | 28 |
未来 | 62 | 29 |
飛び石でないランキングを作る
COUNT(DISTINCT K2.boob)
に書き換えればOK