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 */