Created
December 23, 2021 04:16
-
-
Save kazufusa/dcacf858652d110f16e1cbda0a8027ae to your computer and use it in GitHub Desktop.
達人に学ぶSQL徹底指南書 第2版 1-1. CASE式のススメ の CASE式の中で集約関数を使うサンプル
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/sh | |
# 達人に学ぶSQL徹底指南書 第2版 1-1. CASE式のススメ の CASE式の中で集約関数を使うサンプル | |
# "HAVING句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる" | |
set -Ceux | |
cat << EOF | sqlite3 test.sqlite3 | |
DROP TABLE student_club; | |
CREATE TABLE student_club ( | |
std_id INTEGER NOT NULL, | |
club_id INTEGER, | |
club_name TEXT, | |
main_club_flg BOOL | |
); | |
INSERT INTO student_club ( | |
std_id, | |
club_id, | |
club_name, | |
main_club_flg ) | |
VALUES | |
( 100, 1, '野球', TRUE), | |
( 100, 2, '吹奏楽', FALSE), | |
( 200, 2, '吹奏楽', FALSE), | |
( 200, 3, 'バドミントン', TRUE), | |
( 200, 4, 'サッカー', FALSE), | |
( 300, 4, 'サッカー', FALSE), | |
( 400, 5, '水泳', FALSE), | |
( 600, 6, '囲碁', FALSE) | |
; | |
.header on | |
.mode column | |
SELECT * FROM student_club; | |
SELECT std_id, | |
CASE WHEN COUNT(*) = 1 THEN MAX(club_id) | |
ELSE MAX( | |
CASE WHEN main_club_flg = TRUE THEN club_id | |
ELSE NULL | |
END | |
) END AS main_club | |
FROM student_club | |
GROUP BY std_id; | |
EOF | |
rm -rf test.sqlite3 | |
# $ sh ./test.sh | |
# + cat | |
# + sqlite3 test.sqlite3 | |
# std_id club_id club_name main_club_flg | |
# ---------- ---------- ---------- ------------- | |
# 100 1 野球 1 | |
# 100 2 吹奏楽 0 | |
# 200 2 吹奏楽 0 | |
# 200 3 バドミントン 1 | |
# 200 4 サッカー 0 | |
# 300 4 サッカー 0 | |
# 400 5 水泳 0 | |
# 600 6 囲碁 0 | |
# std_id main_club | |
# ---------- ---------- | |
# 100 1 | |
# 200 3 | |
# 300 4 | |
# 400 5 | |
# 600 6 | |
# + rm -rf test.sqlite3 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment