sql でグルーピングしてその数が２位上だったらその行を削除するというコード

Question

お世話になります。
下記の実装ができず悩んでおります。

環境：SQL Server 2019
言語：SQL

テーブル名：　M_T

ID, name, ID2, ID3   <= カラム
1 , aaa , 1 ,  2
2 , bbb , 1 ,  3 
3 , ccc , 2 ,  2
4 , ddd,  1 ,  3

↑
行

上記のようなテーブルがあった場合に、

ID3 でグルーピングを実施して、そのとき、 ID2 が　１より上（1 < n） だった場合にその行を削除するSQLが思いつかずなやんでおります。

SELECT COUNT(ID2) FROM M_T GROUP BY ID3 HAVING COUNT(ID2) > 1;

と SELECT 文ならかけそうなのですが、これを　DELETE でどのように書いたらいいのかがわかりません。。。
どなたかご教示をお願い致します。

Comments

[sazi]

削除条件として
> ID2 が　１より上（1 < n）
に対してID３のグルーピングを付加する意味はなんですか？
→ID３ごとの最小のID2のデータを残すって事？

「ID2 の順位が　１より上（1 < n）」という意味ですか？

[kotodama]

ID3でグルーピングしたときに、ID２の要素が複数ある場合には全て削除したいという感じです。。。

Answer 1

SELECT COUNT(ID2) FROM M_T GROUP BY ID3 HAVING COUNT(ID2) > 1;

「ID2の『要素』が複数」っていうのがちょっと謎。
『要素』が『データの種類』って意味なら上の文は間違いなので理解のしにくさに拍車をかける。
『要素』が『行の数』って意味ならID2の値は全く関係ないため…ｺﾚｶﾞﾜｶﾗﾅｲ

上の文だとID3が2でも3でもcount(ID2)は2(行)です。
つまりサンプルデータでは「HAVING COUNT(ID2) > 1」は1行も絞れてない。

; with M_T(ID, name, ID2, ID3) as (
	select 1 , 'aaa' , 1 ,  2
	union all select 2 , 'bbb' , 1 ,  3 
	union all select 3 , 'ccc' , 2 ,  2
	union all select 4 , 'ddd',  1 ,  3
)
SELECT ID3
  ,COUNT(ID2) as 行数
FROM M_T
GROUP BY ID3
HAVING COUNT(ID2) > 1;

/*
ID3         行数
----------- -----------
2           2
3           2

(2 行処理されました)
*/

countはdistinct中に入れないと『データの種類』にはならない。

; with M_T(ID, name, ID2, ID3) as (
	select 1 , 'aaa' , 1 ,  2
	union all select 2 , 'bbb' , 1 ,  3 
	union all select 3 , 'ccc' , 2 ,  2
	union all select 4 , 'ddd',  1 ,  3
)
SELECT ID3
  ,COUNT(distinct ID2) as 行数
FROM M_T
GROUP BY ID3
HAVING COUNT(distinct ID2) > 1;

/*
ID3         行数
----------- -----------
2           2

(1 行処理されました)
*/

ID3さえ取れればsazi氏と同じでwhere ID3 in (select ID3 ～)で消せばok

Comments

[kotodama]

ご丁寧に回答して頂きましてありがとうございます。

Answer 2

delete from M_T
where ID3 in (
        select ID3 from M_T group by ID3 having count(*) > 1
      )

件数=要素数　ではなく、要素数での判断なら、

delete from M_T
where ID3 in (
        select ID3 from M_T group by ID3 having count(DISTINCT ID2) > 1
      )

Comments

[kotodama]

ご回答ありがとうございました。
２つ目の実装でうまくいきました！
助かりました！！

ありがとうございます。