sqlの勉強 グループの中で連番になっていないデータやグループを探す方法を考え中

例えば
Aグループの中に複数のデータがありそのレコードの番号はグループごとに連番が割り振られる
ただしデータ削除でグループ内で連番の規則性が損なわれた

グループの中で連番になっていないデータやグループを探したい

どうやって探せば良いか？

一個ずつグループ内で個数と連番になっているかを比較する
num product name
1 ペン A
2 ノート A
1 ペン B
2 ノートB
3 消しゴムB
2 ノートC
3 消しゴムC
4 テープC
5 ホウキC

例えば
Cグループに4つのデータがありそのデータは
2、3、4、5の番号が割り振られた。

本来なら1-5のデータが存在していることが正しい。
連番になっていないとグループ毎に印刷した時に数が合っていないように見える。データは存在しないのだが、見かけが悪い。
なのでグループ毎の個数を数えその個数分をループし連番になっているかを調べ連番になっていないグループとそのデータ群を出力したい。
他にも
2、4、5
1、2、5
といった連番になっていない場合も抽出したい。
つまりグループ内は1つしか存在しなくても必ず1から始まることとする。

対応

select文でできるのか
カーソルで1グループ毎にループして探す。

探すとすれば、少なくとも2以上のデータを持つグループであること

実際のストアドプロシージャはこれから

実際にやってみるとカーソルでやろうとすると無理があるみたい。
そのクエリで作ってみた。

SELECT * FROM Product
SELECT [group],count(*) as groupCount FROM Product GROUP BY [group]
SELECT [group],MAX(NUM) as MaxNum FROM Product GROUP BY [group]

もとのデータ

USE [test01]
CREATE TABLE Product(
	ID Int,
	NUM Int,
	PRODUCT nvarchar(100),
	[GROUP] nvarchar(100)
)

INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(1,1, 'ペン' ,'A')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(2,2, 'ノート' ,'A')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(3,1, 'ペン' ,'B')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(4,2, 'ノート','B')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(5,3, '消しゴム','B')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(6,2, 'ノート','C')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(7,3, '消しゴム','C')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(8,4, 'テープ','C')
INSERT INTO Product(ID,NUM,PRODUCT,[GROUP]) values(9,5, 'ホウキ','C')

/*
ID NUM PRODUCT GROUP
1 1 ペン A
2 2 ノート A
3 1 ペン B
4 2 ノート B
5 3 消しゴム B
6 2 ノート C–Cグループが1から始まらず2から連番になっていて不規則なデータ
7 3 消しゴム C
8 4 テープ C
9 5 ホウキ C
*/
–(aグループ毎の行数とNumのMax値とが同じ場合で出力する、同じ行数を探す

SELECT * FROM Product as a INNER JOIN 
	(SELECT [group],count(*) as groupCount FROM Product GROUP BY [group]) as aTable
	ON a.[group] = aTable.[group] INNER JOIN
	(SELECT [group],MAX(NUM) as MaxNum FROM Product GROUP BY [group]) as bTable
	ON aTable.groupCount = bTable.MaxNum
	/*
	ID	NUM	PRODUCT	GROUP	group	groupCount	group	MaxNum
1	1	ペン	A	A	2	A	2
2	2	ノート	A	A	2	A	2
3	1	ペン	B	B	3	B	3
4	2	ノート	B	B	3	B	3
5	3	消しゴム	B	B	3	B	3
	*/

–(b)同じ行数を探す
SELECT distinct bTable.[group] FROM Product INNER JOIN
(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
GROUP BY [group]) as bTable
ON groupCount = bTable.MaxNum
–(c)同じテーブルを作り、別名にして比較する 同じ行数を探す

SELECT * FROM 
	(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
		GROUP BY [group]) as a INNER JOIN 
	(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
	GROUP BY [group]) as b ON a.MaxNum = b.groupCount
/*
group	MaxNum	groupCount	group	MaxNum	groupCount
A	2	2	A	2	2
B	3	3	B	3	3
*/

–例2aグループ毎の行数とNumのMax値とが違う場合で出力する

SELECT distinct bTable.[group] FROM Product INNER JOIN
	(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
	GROUP BY [group]) as bTable
ON  groupCount != bTable.MaxNum
/*
C
*/

–例2b–グループ毎の行数とNumのMax値とが違う場合で出力する
–同じテーブルを別名にして比較し、WHEREで条件をつける

 SELECT a.[GROUP],a.MaxNum,a.groupCount FROM 
	(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
	GROUP BY [group]) as a INNER JOIN 
	(SELECT [group],MAX(NUM) as MaxNum ,count(*) as groupCount FROM Product
	GROUP BY [group]) as b ON a.MaxNum != b.groupCount
WHERE
	a.[group] = b.[group]
/*
GROUP	MaxNum	groupCount
C	5	4
*/