全体を出力する方法がわからない

Question

前提・実現したいこと

SQLの出力方法でわからないことがあるので教えてください。

【従業員マスタ】
従業員コード 従業員名
A1 山田
A2 田中
B1 佐藤
B2 梨田
C1 鈴木

【年齢マスタ】
従業員コード 年齢
A1 16
A2 14
B1 6
B2 4
C1 3

【種族マスタ】
従業員コード 種族
A1 人間
A2 人間
B1 哺乳類
B2 哺乳類
C1 爬虫類

とあって、種族ごとの平均年齢を出力したいのですがどうもうまくいきません。

---

＜期待している出力＞
従業員コード 従業員名 年齢 種族 平均年齢(種族)
A1 山田 16 人間 15
A2 田中 14 人間 15
B1 佐藤 6 哺乳類 5
B2 梨田 4 哺乳類 5
C1 鈴木 3 爬虫類 3

試したこと

INNER JOINを使って従業員マスタから年齢、種族マスタを結合し、
group byで種族マスタを指定しているのですが、どうも
種族 平均年齢(種族)
人間 15
哺乳類 5
爬虫類 3
という感じにしかならないです。
私としては全体のデータが出力できるようにしたいですがどのようにすればいいでしょうか・・・？

Comments

[unhappychoice]

SQL のコードを掲載してください

[gentaro]

そもそもテーブル設計がおかしいように見えます。
「年齢マスタ」「種族マスタ」というのは「マスタ」ではなく、明らかに「従業員」の従属情報です。
まずテーブルを分ける意味がわかりません。
仮に分ける理由があるとして、あなたが書いたSQLがどんなものなのかを明示しない場合、ただの「丸投げ」です。

[m.ts10806]

従業員と年齢をわける必要性を感じませんが、どういう設計思想でそのようにされているのでしょうか。

Answer 1

INNER JOINを使って従業員マスタから年齢、種族マスタを結合し、
group byで種族マスタを指定しているのですが

私としては全体のデータが出力できるようにしたい

要求は、group byの単位が違うものですから、同時にはできません。

select m1.従業員コード, 従業員名, 年齢, 種族
     , (select avg(年齢) 
        from  年齢マスタ s2 
              inner join 種族マスタ s3  
              on s2.従業員コード=s3.従業員コード
        where s3.種族=m3.種族
       ) as 平均年齢(種族)
from 従業員マスタ m1 
     inner join 年齢マスタ m2
     on m1.従業員コード=m2.従業員コード
     inner join 種族マスタm3
     on m1.従業員コード=m3.従業員コード

分析関数の使えるDBMSなら簡潔な記述にできます。

select m1.従業員コード, 従業員名, 年齢, 種族
     , avg(年齢) over(partition by 種族) as 平均年齢(種族)
from 従業員マスタ m1 
     inner join 年齢マスタ m2
     on m1.従業員コード=m2.従業員コード
     inner join 種族マスタm3
     on m1.従業員コード=m3.従業員コード