sql server查询除ID外重复的所有记录

sql server当中想查询除ID外重复的所有记录，即查找除了ID外相同记录大于等于2条记录。

img

根据其他字段分组 然后计算count>=2的 然后关联查询也行，in也行

select *
  from table
 where (股票代码, 日期, 股价, 成交量) in (select 股票代码, 日期, 股价, 成交量
                                 from table
                                group by 股票代码, 日期, 股价, 成交量
                               having count(1) > 1)

with t1 as (
select '8646726' id,'002118' type,'20221101' dt,'11' gj,5000 amt union
select '8646725' id,'002118' type,'20221101' dt,'11' gj,5000 amt union
select '8646724' id,'002479' type,'20221101' dt,'58' gj,4000 amt union
select '8646701' id,'002479' type,'20221101' dt,'58' gj,4000 amt union
select '8646743' id,'000002' type,'20221101' dt,'14' gj,4000 amt
) -- 测试数据可以忽略
SELECT * FROM (
selecT id,TYPE,DT,GJ,amt,ROW_NUMBER()OVER(PARTITION BY TYPE,DT,GJ,AMT ORDER BY TYPE DESC) RO
FROM T1 ) T1
WHERE T1.RO > 1