SQL指南書　HAVING句①　データの歯抜けを探す

SQL 指南書集目次 - ray88’s diary
■HAVING句のポイント
・SQLは複数行をまとめて集合として扱う。つまりテーブル全体は一つの集合とみなすことができる
・HAVING句はGROUP BY句がなくても使用できる
・GROUP BY句が存在しない場合はテーブル全体が１行に集約される
・テーブルはファイルではないので、行に順序はない。それゆえ、行同士の比較を行う際にソートをしない
■例題
以下の連番には歯抜けがある。データの歯抜けの有無を確認する

SELECT '歯抜けあり' AS gap
FROM seqtbl
HAVING COUNT(*) <> MAX(seq);

【SQL解説】
COUNT(*)で数えた行数と連番の最大値が一致した場合は歯抜けはなく抽出レコードはなし
歯抜けありの場合は COUNT(*) < MAX(seq)となり、HAVING句の条件が真となり「歯抜けあり」を返す

【ロジック解説】
集合論で解説すると、自然数の集合とseqtbl集合の間に一対一対応（全単射）が存在するかをテストしているということになる。

■例題２
歯抜けの最小値を求める

--歯抜けの最小値を探す
SELECT MIN(seq + 1) AS gap
FROM seqtbl
WHERE(seq + 1) NOT IN(SELECT seq FROM seqtbl);

【ロジック解説】
NOT IN を使用したサブクエリで、ある連番についてそれより１つ大きい数値がテーブル内に存在するかどうかを調べる
つまり(3.ライル),(6.マリー),(8.ベン)の行について、次の数が見つからないため、条件が真になる。
歯抜けがない場合は、最大の連番8の次の数である9が得られる。

■応用例題
これまでは歯抜けがある場合のみ結果が出力されるクエリだったが、
これを歯抜けがある場合は「歯抜けあり」ない場合は「歯抜けなし」と必ず結果を１行で返すクエリを作成する

回答１

SELECT CASE WHEN COUNT(*) <> MAX(seq)
            THEN '歯抜けあり'
            ELSE '歯抜けなし' END AS gap
  FROM SeqTbl;

回答２

/* 演習問題１：常に結果を一行返す欠番チェック(p.292) */
SELECT ' 歯抜けあり' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)
UNION ALL
SELECT ' 歯抜けなし' AS gap
  FROM SeqTbl
HAVING COUNT(*) = MAX(seq);

【参考】テーブル作成SQL

/* データの歯抜けを探す */
CREATE TABLE SeqTbl
(seq  INTEGER PRIMARY KEY,
 name VARCHAR(16) NOT NULL);

INSERT INTO SeqTbl VALUES(1,	'ディック');
INSERT INTO SeqTbl VALUES(2,	'アン');
INSERT INTO SeqTbl VALUES(3,	'ライル');
INSERT INTO SeqTbl VALUES(5,	'カー');
INSERT INTO SeqTbl VALUES(6,	'マリー');
INSERT INTO SeqTbl VALUES(8,	'ベン');

歯抜けなしデータの場合のデータ挿入

INSERT INTO SeqTbl VALUES(1,	'ディック');
INSERT INTO SeqTbl VALUES(2,	'アン');
INSERT INTO SeqTbl VALUES(3,	'ライル');
INSERT INTO SeqTbl VALUES(4,	'トム');
INSERT INTO SeqTbl VALUES(5,	'カー');
INSERT INTO SeqTbl VALUES(6,	'マリー');
INSERT INTO SeqTbl VALUES(7,	'ジェリー');
INSERT INTO SeqTbl VALUES(8,	'ベン');