【SQL Server】異なるレイアウトのテーブル結合方法

Question

SQL初心者です。
現在、ある1つのユーザテーブル(練習台)があります。
テーブルの定義内容は以下です。

テーブル名：usr

列名　      　データ型・制約など                 概要   
user_id      CHAR(7) NOT NULL PRIMARY KEY  利用者コード
l_name       VARCHAR(20)                   苗字
f_name       VARCHAR(20)                   名前
l_name_kana  VARCHAR(100)                  苗字（カナ）
f_name_kana  VARCHAR(100)                  名前（カナ）
prefecture   VARCHAR(15)                   住所（都道府県）
city         VARCHAR(20)                   住所（市）
o_address    VARCHAR(100)                  住所（その他）
tel          VARCHAR(20)                   電話番号
email        VARCHAR(255)                  メールアドレス

問題が4つ用意されており、[問題4]の糸口を見つけられずにいます。順番にいきます。

問題1：
usrテーブルから 利用者コード、氏名、氏名（カナ）、住所を取り出せ。

SELECT 
   user_id, 
   CONCAT(l_name, f_name) AS 名前, 
   CONCAT(l_name_kana, f_name_kana) AS 名前カナ,
   CONCAT(prefecture, city, o_address) AS 住所
FROM usr
;

user_id     名前       名前カナ      住所
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
A0001     名無太郎    ナナシタロウ    東京都・・・・・
A0002     ・・・・・
　・
A0014
B0001     音無響子    オトナシキョウコ  岡山県津山町・・・
　・
　・
B0015  ←最終行

問題2：
usrテーブルから 利用者コード、氏名、氏名（カナ）、住所を取り出せ。
※f_name_kanaが「コ」で終わる人だけ

SELECT 
   user_id, 
   CONCAT(l_name, f_name) AS 名前, 
   CONCAT(l_name_kana, f_name_kana) AS 名前カナ,
   CONCAT(prefecture, city, o_address) AS 住所
FROM 
   usr
WHERE
   f_name_kana LIKE '%コ'
;

user_id     名前       名前カナ      住所
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
B0001     音無響子    オトナシキョウコ  岡山県津山町・・・
　・
　・
全5人(絞り込んだ結果仮に5人とする)

問題3：
usr_id列で「Aから始まるID」、「Bから始まるID」の人数を集計、A列、B列として出力せよ

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
;

  A     B  
ーーーーーーー
 14人  15人 
(全レコード数は29件、IDがA始まりが14人、Bが15人とする)

問題4：
usrテーブルから [問題2]、[問題3]の情報を取り出せ。但し結果は一緒に表示しろ
条件：サブクエリは使用しない・・実際環境はデータ数が数万件規模を想定
↓↓
答えとして欲しいのは下のような出力結果です

user_id     名前       名前カナ      住所        A人数   B人数
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
B0001     音無響子    オトナシキョウコ  岡山県・・・    14       15
　・　　　　　 ・・・　　　　　・・・・　　　　　・・・・　　　　NULL     NULL
　・         ・・・　　　　　・・・・　　　　　・・・・　　　　NULL     NULL
全5人

現状思いつく限りで書いたクエリ文が、

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 '％コ'

user_id     名前       名前カナ      住所        A人数   B人数
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
B0001     音無響子    オトナシキョウコ  岡山県・・・     14      15
　・　　　　　・・・　　　　　・・・・　　　　　・・・・　　      14      15
　・         ・・・　　　　　・・・・　　　　　・・・・　　     14      15
全5人

A, B人数の集計結果が全行に入ってしまっているので、一行目だけに集計結果を表示できれば、と思っていましたが、サブクエリ文は便利な一方で処理数が増加傾向にあり重くなりがちです。

ゆくゆくデータ数が数万件レベルを想定し、サブクエリの利用は避けて、クエリ文を仕上げたいと考えておりますが、結合で理想のビューを出力するのに方向性が全くわきません。

何かご存知の方がいらっしゃればぜひともご教示いただけますと幸いです。
よろしくお願いします。

Comments

[sousuke]

問題4で「usrテーブルから [問題2]、[問題4]の情報を取り出せ。但し結果は一緒に表示しろ」
とあるので混乱します。[問題2]、[問題3]でいいんですかね？
正解のoutputを書いたほうがいいと思います。

[sumAA]

sousukeさん

こんばんは。
申し訳ありません。問題2、問題3の間違いです、
質問文は修正しました。ご指摘ありがとうございます！！

Answer 1

ヒントは GROUP BY

SQL Server SELECT SUM GROUP BY

でググって使い方を調べると良いでしょう。

Comments

[sumAA]

Orlofskyさん

こんにちは。
返信が遅くなってしまい申し訳ありません。

コメントありがとございます。
一度このキーワードで調べてみます！

[sumAA]

無事解決しました。
結論、早々に除外した「UNION」を使用することで求める結果を得ることができました。まさか、UNIONでつなぐときに、レイアウトの違うテーブル列を「NULL」や「''」でダミー列を作って対応できるとは驚きでした（いいか悪いかは別として）

集計列だけSELECTする場合はGROUP BYを使用しなくてもよいこととうまく結びつけることができました。ありがとうございました！！

Answer 2

自己解決しました。

結論：
UNION 又は UNION ALLを使用する。
列が合わない箇所は「''」「NULL」などでダミー列とし対応する。
※データ型を意識しないとエラーになる場合あり。 　例：INT列に文字列型を適用しようとするなど

SELECT 
   u.user_ID AS ID,
   CONCAT(u.l_name, u.f_name) AS Full_Name,
   CONCAT(u.l_name_kana, u.f_name_kana) AS Name_Kana
   CONCAT(u.prefecture, u.city, u.o_address) AS Address
   NULL AS A,
   NULL AS B
FROM
   usr AS u
WHERE
   u.f_name_kana LIKE '%コ'
UNION
SELECT
   '***' AS ID,
   '***' AS Full_Name,
   '***' AS Name_Kana,
   '***' AS Address,
   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 cont

user_id     名前       名前カナ      住所        A人数   B人数
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
 ***        ***         ***        ***　      14      15
B0001     音無響子    オトナシキョコ  岡山県・・・     NULL    NULL
B0003     音無本子    オトナシモトコ  岡山県・・・     NULL    NULL
B0009     音無問子    オトナシトンコ  岡山県・・・     NULL    NULL
B0011     音無萌子    オトナシモエコ  岡山県・・・     NULL    NULL
B0014     音無何子    オトナシナニコ  岡山県・・・     NULL    NULL

全6行（全5人）

※追記
無駄に列数を増やしたくなければ、

SELECT 
   u.user_ID AS ID,
   CONCAT(u.l_name, u.f_name) AS Full_Name,
   CONCAT(u.l_name_kana, u.f_name_kana) AS Name_Kana
   CONCAT(u.prefecture, u.city, u.o_address) AS Address
FROM
   usr AS u
WHERE
   u.f_name_kana LIKE '%コ'
UNION
SELECT
   'A' AS ID,
    CAST(SUM(CASE WHEN cont.user_id LIKE 'A%' THEN 1 ELSE 0 END) as VARCHAR) AS Full_Name,
   'B' AS Name_Kana,
    CAST(SUM(CASE WHEN cont.user_id LIKE 'B%' THEN 1 ELSE 0 END) as VARCHAR) AS Address,
FROM
   usr AS cont

user_id     名前       名前カナ      住所     
ーーーーーーーーーーーーーーーーーーーーーーーーーー
   A        14           B          15　     
B0001     音無響子    オトナシキョコ  岡山県・・・   
B0003     音無本子    オトナシモトコ  岡山県・・・   
B0009     音無問子    オトナシトンコ  岡山県・・・   
B0011     音無萌子    オトナシモエコ  岡山県・・・  
B0014     音無何子    オトナシナニコ  岡山県・・・  

全6行（全5人）

とすることもできます。

最初に選択肢から弾いたUNIONを利用することになるとは驚きました。

実務に携わる方にも質問してみたところ、
列レイアウトの異なるテーブル同士をUNIONすることは一般的でないことは前提におきつつも、実際問題としてクライアントの要望によって一般的でない手法を用いて上記のように情報をくっつけてしまうケースはゼロではないとの回答でした。

質問をさせていただいた時点では、
目的のテーブルを取得するのに、方向性どころか何が分からないのか分からない状況だった故に、意図を汲みにくいスレを立ててしまうことになり申し訳ありませんでした。

これからも少しずつですが精進して参ります。
拙い文章にも関わらず助言くださったみなさま、ありがとうございました。

Comments

[sousuke]

すでに終わった質問に対してアレなのですが
確定で1行であればcross joinでいいと思いました。
未検証ですが要領はfrom句にガッツリ突っ込むだけです。

SELECT
u.user_ID AS ID,
CONCAT(u.l_name, u.f_name) AS Full_Name,
CONCAT(u.l_name_kana, u.f_name_kana) AS Name_Kana
CONCAT(u.prefecture, u.city, u.o_address) AS Address
--NULL AS A,
--NULL AS B
,Z.A,Z.B
FROM
usr AS u
cross join (
SELECT
'***' AS ID,
'***' AS Full_Name,
'***' AS Name_Kana,
'***' AS Address,
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 cont
) as Z
WHERE
u.f_name_kana LIKE '%コ'

[sumAA]

いえ、ご丁寧にありがとうございます！！
cross joinという結合方法もあるのですね。

今別件で立て込んでいるのですが、
落ち着いたら僕の環境で動作の検証してみます！！
ありがとうございます！

[sumAA]

上記のコードを実行環境にいれてみたところ、以下のようなビューが出力されました。inner join, left outer joinしか使ったことがなかったのですが、こんなこともできるのですね！！ありがとございます。

B200404 和田修子 ワダシュウコ 東京都東京市北町6-1-11 11 10
B200502 村井佐知子 ムライサチコ 千葉県東千葉市北町4-1-13 11 10
B200504 西島文子 ニシジマフミコ 栃木県北栃木市東町2-5-7 11 10
B200505 根岸裕子 ネギシユウコ 静岡県南静岡市寺町3-4-8 11 10