SQL初心者です。
現在、ある1つのユーザテーブル(練習台)があります。
テーブルの定義内容は以下です。
テーブル名:usr
SQL
1列名 データ型・制約など 概要 2user_id CHAR(7) NOT NULL PRIMARY KEY 利用者コード 3l_name VARCHAR(20) 苗字 4f_name VARCHAR(20) 名前 5l_name_kana VARCHAR(100) 苗字(カナ) 6f_name_kana VARCHAR(100) 名前(カナ) 7prefecture VARCHAR(15) 住所(都道府県) 8city VARCHAR(20) 住所(市) 9o_address VARCHAR(100) 住所(その他) 10tel VARCHAR(20) 電話番号 11email VARCHAR(255) メールアドレス
問題が4つ用意されており、[問題4]の糸口を見つけられずにいます。順番にいきます。
問題1:
usrテーブルから 利用者コード、氏名、氏名(カナ)、住所を取り出せ。
SQL
1SELECT 2 user_id, 3 CONCAT(l_name, f_name) AS 名前, 4 CONCAT(l_name_kana, f_name_kana) AS 名前カナ, 5 CONCAT(prefecture, city, o_address) AS 住所 6FROM usr 7;
OutPut
1user_id 名前 名前カナ 住所 2ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 3A0001 名無太郎 ナナシタロウ 東京都・・・・・ 4A0002 ・・・・・ 5 ・ 6A0014 7B0001 音無響子 オトナシキョウコ 岡山県津山町・・・ 8 ・ 9 ・ 10B0015 ←最終行
問題2:
usrテーブルから 利用者コード、氏名、氏名(カナ)、住所を取り出せ。
※f_name_kanaが「コ」で終わる人だけ
SQL
1SELECT 2 user_id, 3 CONCAT(l_name, f_name) AS 名前, 4 CONCAT(l_name_kana, f_name_kana) AS 名前カナ, 5 CONCAT(prefecture, city, o_address) AS 住所 6FROM 7 usr 8WHERE 9 f_name_kana LIKE '%コ' 10;
OutPut
1user_id 名前 名前カナ 住所 2ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 3B0001 音無響子 オトナシキョウコ 岡山県津山町・・・ 4 ・ 5 ・ 6全5人(絞り込んだ結果仮に5人とする)
問題3:
usr_id列で「Aから始まるID」、「Bから始まるID」の人数を集計、A列、B列として出力せよ
SQL
1SELECT 2 SUM( CASE WHEN user_id LIKE 'A%' THEN 1 ELSE 0 END ) AS A 3 SUM( CASE WHEN user_id LIKE 'B%' THEN 1 ELSE 0 END ) AS B 4FROM 5 usr 6;
OutPut
1 A B 2ーーーーーーー 3 14人 15人 4(全レコード数は29件、IDがA始まりが14人、Bが15人とする)
問題4:
usrテーブルから [問題2]、[問題3]の情報を取り出せ。但し結果は一緒に表示しろ
条件:サブクエリは使用しない・・実際環境はデータ数が数万件規模を想定
↓↓
答えとして欲しいのは下のような出力結果です
OutPut
1user_id 名前 名前カナ 住所 A人数 B人数 2ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 3B0001 音無響子 オトナシキョウコ 岡山県・・・ 14 15 4 ・ ・・・ ・・・・ ・・・・ NULL NULL 5 ・ ・・・ ・・・・ ・・・・ NULL NULL 6全5人 7
現状思いつく限りで書いたクエリ文が、
SELECT u.user_id, CONCAT(u.l_name, u.f_name) AS 名前, CONCAT(u.l_name_kana, u.f_name_kana) AS 名前カナ, CONCAT(u.prefecture, u.city, u.o_address) AS 住所, SUM( CASE WHEN cont.user_id LIKE 'A%' THEN 1 ELSE 0 END ) AS A, SUM( CASE WHEN cont.user_id LIKE 'B%' THEN 1 ELSE 0 END ) AS B FROM usr AS u INNER JOIN usr AS cont ON u.user_id = cont.user_id WHERE u.f_name_kana LIKE '%コ' GROUP BY u.user_id, CONCAT(u.l_name, u.f_name), CONCAT(u.l_name_kana, u.f_name_kana) , CONCAT(u.prefecture, u.city, u.o_address)
なのですが、この結果は、
user_id 名前 名前カナ 住所 A人数 B人数 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー B0001 音無響子 オトナシキョウコ 岡山県・・・ 0 1 ・ ・・・ ・・・・ ・・・・ 0 1 ・ ・・・ ・・・・ ・・・・ 0 1 全5人
となってしまいます。
集計関数SUMを使用しているのでGROUP BYは必須となります。
GROUP BYが必須となれば必然的に集計列以外をグループ化しないとなりません。
(今回だと、user_id, 名前, 名前カナ, 住所)
自分で書いたクエリ文の結果が上記のようになるのは当然ではあるのですが、
理想の出力結果を得るクエリ文を記載するにあたり
方向性を見出せない状況になってしまっています。
UNIONでもできないか以下のように試してみました。
SELECT u.user_id, CONCAT(u.l_name, u.f_name) AS 名前, CONCAT(u.l_name_kana, u.f_name_kana) AS 名前カナ, CONCAT(u.prefecture, u.city, u.o_address) AS 住所 FROM usr AS u WHERE u.f_name_kana LIKE '%コ' UNION SELECT SUM( CASE WHEN user_id LIKE 'A%' THEN 1 ELSE 0 END ) AS A SUM( CASE WHEN user_id LIKE 'B%' THEN 1 ELSE 0 END ) AS B FROM usr
結果はエラー文が返ってきました。
問題2と問題3とではテーブルの構造が異なるのでそうなるのは当然でしょう。
以下サブクエリを利用すれば似たような次のような出力結果は得ることができました。
SELECT user_id, CONCAT(l_name, f_name) AS 名前, CONCAT(l_name_kana, f_name_kana) AS 名前カナ, CONCAT(prefecture, city, o_address) AS 住所, (SELECT DISTINCT COUNT(user_id) FROM usr WHERE user_id LIKE 'A%') AS A, (SELECT DISTINCT COUNT(user_id) FROM usr WHERE user_id LIKE 'B%') AS B, FROM usr WHERE f_name_kana LIKE '%コ'
output
1user_id 名前 名前カナ 住所 A人数 B人数 2ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 3B0001 音無響子 オトナシキョウコ 岡山県・・・ 14 15 4 ・ ・・・ ・・・・ ・・・・ 14 15 5 ・ ・・・ ・・・・ ・・・・ 14 15 6全5人
A, B人数の集計結果が全行に入ってしまっているので、一行目だけに集計結果を表示できれば、と思っていましたが、サブクエリ文は便利な一方で処理数が増加傾向にあり重くなりがちです。
ゆくゆくデータ数が数万件レベルを想定し、サブクエリの利用は避けて、クエリ文を仕上げたいと考えておりますが、結合で理想のビューを出力するのに方向性が全くわきません。
何かご存知の方がいらっしゃればぜひともご教示いただけますと幸いです。
よろしくお願いします。
回答2件
あなたの回答
tips
プレビュー