大グループの中で複数の小グループに属している人の人数を知りたい

Question

質問させていただきます。

タイトルにもある通り、
大グループの中で、複数の小グループに属している人の人数を知りたいのですが、
sqlが思いつかない状態です。
お力を貸していただけないでしょうか。

【前提条件】
・SQLは方言に依存しないものを使用する（標準SQLで出したい）
・小グループのコードは重複しない
・ID（要は人）は他の大グループに所属していることもあるが大グループ毎に別人と考える
・複数小グループに属する数が2以上であればカウント

【得たい結果】

LARGE_GROUP	人数
A	1
B	2
C	4

【テーブル内のデータ】
G_TABLE：グループテーブル

LARGE_GROUP	SMALL_GROUP	ID
A	101	X000001
A	102	X000001
A	101	X000002
A	102	X000003
B	201	Y000001
B	202	Y000001
B	201	Y000002
B	202	Y000002
C	301	X000001
C	302	X000001
C	303	X000001
C	301	Z000001
C	302	Z000001
C	301	Z000002
C	303	Z000002
C	304	Z000003
C	305	Z000003

【人数の内訳】
大グループA
・X000001が101と102の小グループに属している。
大グループB
・Y000001が201と202に属している
・Y000002が201と202に属している
大グループC
・X000001が301と302と303に属している
・Z000001が301と302に属している
・Z000002が301と303に属している
・Z000003が304と305に属している

【自分で思いつき、試してみたSQL】

SELECT
 LARGE_GROUP,
 COUNT(ID) AS 人数
FROM
 G_TABLE origin
inner join
(
 SELECT
  KAKEMOCHI.LARGE_GROUP
  KAKEMOCHI.KAKEMOCHI_ID
 FROM (
  SELECT
   LARGE_GROUP AS KAKEMOCHI_ID_LG,
   ID AS KAKEMOCHI_ID,
   COUNT(SMALL_GROUP) AS JOINCOUNT
  FROM
   G_TABLE
  GROUP BY
   LARGE_GROUP,
   ID
　HAVING JOINCOUNT > 1
 ) KAKEMOCHI --複数小グループに属している人の大グループとIDを出したテーブル
ON
    origin.LARGE_GROUP = KAKEMOCHI.LARGE_GROUP
AND origin.ID = KAKEMOCHI.ID
GROUP BY
 origin.LARGE_GROUP

よろしくお願いします。

Comments

[YT0014]

例における期待する結果表をご提示ください。
また、エラーや誤った結果になったとしても、思いつくSQLを作成して、試した結果をご提示ください。
現状では、丸投げの状態です。

[kukku-zelda]

まずは申し訳ありません。
期待する結果表は【得たい結果】に書いたつもりでした。
＞また、エラーや誤った結果になったとしても、思いつくSQLを作成して、試した結果をご提示ください。
こちら追記させていただきます。

[m.ts10806]

「SQL」といっても方言が多いです。
想定しているDBとバージョンを追記してください。

[YT0014]

＞期待する結果表は【得たい結果】に書いたつもりでした。
きちんと記載がありました。読み間違えていました。失礼しました。

Answer 1

大、中、小のグループがあるような質問文ですがどうみても大と小しかない件。

D 401 X000002 という行が仮にあったらどうするのか。ｺﾚｶﾞﾜｶﾗﾅｲ

select LARGE_GROUP, count(distinct id)
from G_TABLE A
where (
       select count(*) from G_TABLE B
       where B.ID=A.ID
          and B.LARGE_GROUP + ' ' + B.SMALL_GROUP <> A.LARGE_GROUP + ' ' + A.SMALL_GROUP
      ) > 0
group by LARGE_GROUP
order by LARGE_GROUP

Comments

[sazi]

>・SQLは方言に依存しないものを使用する（標準SQLで出したい）
どう見ても標準じゃない件

[sousuke]

みんな自分が使っているものを標準だと思っているから…（震え）
SQLServerに慣れるとAccessのクエリで「こんなこともできねえの？はぁ～つっかえ！」ってなる。

[sazi]

Dを入れると、希望の結果とは違ってしまうんですね。(ｿﾚﾊﾀﾞﾒｼﾞｬﾅｲｶﾅ）

[sousuke]

Dの件は仕様的にも少し悩んだんで別解としてあげました笑

[kukku-zelda]

＞大、中、小のグループがあるような質問文ですがどうみても大と小しかない件。
大変失礼いたしました。
【前提】の箇所を修正しました。
誤）・ID（要は人）は他の中グループに所属していることもあるが中グループ毎に別人と考える
↓
正）・ID（要は人）は他の大グループに所属していることもあるが大グループ毎に別人と考える
大・小の二つのグループです。
混乱する文面で申し訳ありませんでした。

[kukku-zelda]

＞D 401 X000002 という行が仮にあったらどうするのか。ｺﾚｶﾞﾜｶﾗﾅｲ
この場合は対象とならないので、「抽出なし」で大丈夫でした。
ご回答ありがとうございました。

[sousuke]

そうなると仕様が違いますね？Dのデータがある場合、X000002は複数小グループに所属しています。
・複数小グループに属する数が2以上であればカウント✕
・同一大グループ内で小グループに属する数が2以上であればカウント○

[kukku-zelda]

【前提】のところなのですが、
他の大グループに所属していることもあるが大グループ毎に別人と考える
なので、
後者の
同一大グループ内で小グループに属する数が2以上であればカウント○
になる認識です！

[sousuke]

いや質問者の認識ではなくて回答者に与える情報の話です。
・複数小グループに属する数が2以上であればカウント
とあれば「D 401 X000002」という行があった場合「X000002」は
どうみても『複数の小グループに属しています』よね？
『他の大グループに所属していることもあるが大グループ毎に別人と考える』のでというのは
配慮に欠ける説明だと思います。現に私はほんの少し悩み、sazi氏も勘違いしました。

[sazi]

私の勘違いは、この回答のSQLの結果についてで、質問内容の取り違えは（結果的に）無かったようです。

[sousuke]

sazi氏はそっちの方だったんですね笑
まあいずれにしてもプログラム書いているなら、正確に伝えましょう～

Answer 2

select large_group, count(distinct id)
from G_TABLE t
where (
       select count(*) from G_TABLE
       where large_group = t.large_group and id = t.id
      ) > 1
group by large_group
order by large_group

または

select large_group, count(distinct id)
from G_TABLE t
where exists(
        select 1 from G_TABLE
        where large_group = t.large_group and id = t.id and small_group != t.small_group
      )
group by large_group
order by large_group

Comments

[kukku-zelda]

ありがとうございます！
大変助かりました！

考え方をきちんと理解できるまで、上記SQLを読み込みます！