Oracle 副問い合わせ 集計

Question

いつも大変お世話になっております。

表A 表B
ーーーーーーーーーー －－－－－－－－－－－
会社名 会社CD 会社CD 従業員名
〇〇会社｜会社コード 会社コード｜☆☆さん
△△会社｜会社コード 会社コード｜★★さん
◇◇会社｜会社コード 会社コード｜＆＆さん
〇〇会社｜会社コード 会社コード｜＆＆さん
◇◇会社｜会社コード 会社コード｜★★さん
〇〇会社｜会社コード 会社コード｜☆☆さん

上記の様な表がありまして、
会社コードで内部結合をし、どに会社に一番従業員がいるかを集計し、一番従業員が多い会社の従業員一覧を表示させたいのです。

結果イメージ
ーーーーーーーーーーー
＆＆さん
☆☆さん
★★さん
＆＆さん
☆☆さん
☆☆さん
★★さん

の様な感じです。

SELECT
MAX(従業員名)
FROM
(SELECT
会社CD,
従業員名
FROM
表A HA INNER JOIN 表B HB
ON (HA.会社CD = HB.会社CD)
GROUP BY
会社名,
従業員名)T1,
(SELECT
COUNT(従業員名) as syain,
会社名
FROM
表B)T2

という感じで書いてみたのですが、書き直しても書き直してもエラーが出てしまって出力できません･･･。
どうすればよいのでしょうか？

会社CD＝CHAR
従業員名＝VARCHAR2
会社名＝CHAR

Comments

[gusao]

どのようなエラーが出るのでしょうか？また、「従業員名」「社員名」は同じものですか？その場合、どちらかに統一して質問文の修正をお願いします。

[tentem]

大変申し訳ありません。
従業員名と社員名は同じものでございます。

自分で色々と試行錯誤しながら修正し、修正しで書いていて
色々なエラーが出てしまっているのですが、
この状態でのエラーは「表Bは無効です」
と表示されております。

Answer 1

これでいかがでしょう。

select 表A.会社名,表B.従業員名 from 表B,表A,
	(
		select 会社CD,rank() over(order by 従業員数 desc,会社CD) as ランク
		from 
			(select 会社CD,count(*) 従業員数 from 表B a group by 会社CD)
	) 表C
where 表A.会社CD = 表B.会社CD
and 表A.会社CD = 表C.会社CD and ランク = 1;

rank関数で、従業員が多い会社CD、従業員が同じ場合は会社CDの若い番号を取得します。
ランクの引数を変えれば2番目に多い会社の一覧も出せます。

Answer 2

自分だったらこういう一発調査系は

SELECT 会社CD
FROM (SELECT 会社CD,COUNT(*) AS NUM FROM 表B GROUP BY 会社CD)
ORDER BY NUM DESC
WHERE ROWNUM=1;

これで該当会社コードを取り出して

SELECT 従業員名 FROM 表B WHERE 会社CD='xxx';

あんまり無理してまで一発処理する必要はないんじゃないかなあ、と。

Answer 3

こんな感じでどうですかね？

select 会社名,従業員名
from   表A inner join 表B on 表A.会社CD = 表B.会社CD
where  表A.会社CD = (
    select 会社CD 
    from (
      select 会社CD,count(従業員名) as SHAIN_COUNT
      from  表B 
      group by 会社CD
      order by SHAIN_COUNT DESC
      )
    where ROWNUM = 1
    )