HAVING句の活用　発展編

投稿日：2016年12月3日

今回からはまたまたHAVING句です。

下記のようなテーブル(teams)があり、全員が待機状態のチーム(全員がそろっている)を求めます。

member | team_id | status
——————+———+————-
ジョー | 1 | 待機
ケン | 1 | 出動中
ミック | 1 | 待機
カレン | 2 | 出動中
キース | 2 | 休暇
ジャン | 3 | 待機
ハート | 3 | 待機
ディック | 3 | 待機
ベス | 4 | 待機
アレン | 5 | 出動中
ロバート | 5 | 休暇
ケーガン | 5 | 待機

回答は下記のようになります。

team_id
———
4
3

これをNOT EXISTSとHAVINGを使って２パターンで記述する問題なのです。

HAVING

SELECT
  team_id
FROM
  teams
GROUP BY team_id
HAVING COUNT(*) = SUM( ( CASE status WHEN '待機' THEN 1 ELSE 0 END ) )

SELECT

team_id

FROM

teams

GROUP BY team_id

HAVING COUNT(*) = SUM( ( CASE status WHEN '待機' THEN 1 ELSE 0 END ) )

NOT EXISTS

SELECT
 DISTINCT t1.team_id
FROM
  teams t1
WHERE
   NOT EXISTS
  (
   SELECT
       *
   FROM  
     teams t2
   WHERE
     t2.status <> '待機'  AND
     t1.team_id = t2.team_id
);

SELECT

DISTINCT t1.team_id

FROM

teams t1

WHERE

NOT EXISTS

(

SELECT

FROM

teams t2

WHERE

t2.status <> '待機' AND

t1.team_id = t2.team_id

);

本にも書いてありましたが、私もHAVINGのほうがわかりやすいですね。

CASEとHAINGは見た目がごつい割には理解しやすくフラグ的な扱いができるのでプログラマには向いている気がします。

ちなみにHAVINGを使って下記のように書く回答もありました。

SELECT
  team_id
FROM
  teams
GROUP BY team_id
HAVING MAX(status) = MIN( status )

SELECT

team_id

FROM

teams

GROUP BY team_id

HAVING MAX(status) = MIN( status )

一瞬わからなかったのですが、これはもしteam_id単位で複数のstatusが存在していたら最大と最小が違うという意味なんですね。分かりにくい場合はstatusを数字で考えるとよいと思います。

次は下記のテーブルで拠点ごとに資材のダブりをみるSQLです。

center | receive_date | material
—————–+————–+——————–
東京 | 2007-04-01 | 錫
東京 | 2007-04-12 | 亜鉛
東京 | 2007-05-17 | アルミニウム
東京 | 2007-05-20 | 亜鉛
大阪 | 2007-04-20 | 銅
大阪 | 2007-04-22 | ニッケル
大阪 | 2007-04-29 | 鉛
名古屋 | 2007-03-15 | チタン
名古屋 | 2007-04-01 | 炭素鋼
名古屋 | 2007-04-24 | 炭素鋼
名古屋 | 2007-05-02 | マグネシウム
名古屋 | 2007-05-10 | チタン
福岡 | 2007-05-10 | 亜鉛
福岡 | 2007-05-28 | 錫

下記のレコードが出力されればOKです。

center
—————–
東京
名古屋

これまた回答を見るとなんの変哲もないですが、意外にロジックが考えにくい題材だと思います。

要はダブりがなければ全体の列数とdistinctなmaterialの列数が一緒になるんですね。なのでそうなっていないケースを求めます。

SELECT
  center 
FROM
  materials
GROUP BY center
HAVING COUNT(*) <> COUNT( DISTINCT( material) )

SELECT

center

FROM

materials

GROUP BY center

HAVING COUNT(*) <> COUNT( DISTINCT( material) )

ちなみに下記の書いてあげると全てのcenterのstatusがわかり大変みやすいです。

SELECT
  center, 
  CASE WHEN  COUNT(*) <> COUNT( DISTINCT( material) ) THEN 'ダブりあり' ELSE 'ダブりなし' END AS status 
FROM
  materials
GROUP BY center

SELECT

center,

CASE WHEN COUNT(*) <> COUNT( DISTINCT( material) ) THEN 'ダブりあり' ELSE 'ダブりなし' END AS status

FROM

materials

GROUP BY center

ちなみにEXISTSでもいけます。こちらだとダブっている素材もわかりますね。

SELECT
  --わかりにくければdistinctは最初外してもいいかかも。
  DISTINCT m1.center,  m1.material
FROM
  materials m1
WHERE
  EXISTS (
    SELECT 
       m2.*
    FROM
       materials m2
    WHERE
       m1.center = m2.center AND
       m1.material = m2.material AND
       --日付が合致していないことを入れないと完全に同一データ=自分が入ってきてしまう。
       m1.receive_date <> m2.receive_date  
);

SELECT

--わかりにくければdistinctは最初外してもいいかかも。

DISTINCT m1.center, m1.material

FROM

materials m1

WHERE

EXISTS (

SELECT

m2.*

FROM

materials m2

WHERE

m1.center = m2.center AND

m1.material = m2.material AND

--日付が合致していないことを入れないと完全に同一データ=自分が入ってきてしまう。

m1.receive_date <> m2.receive_date

);

HAVING句の活用 発展編

HAVING句の活用　発展編