select Spec,typeid,brand,encapsulation,area from Product group by Spec,typeid,brand,encapsulation,area
select * from ( select Spec,typeid,brand,encapsulation,area,dense_rank() over(partition by Spec,typeid,brand,encapsulation,area order by Spec,typeid,brand,encapsulation,area desc ) igroup from Product) PD where PD.igroup > 1
--查询过滤重复: ;WITH t AS ( SELECT rn = ROW_NUMBER()OVER( PARTITION BY spec ORDER BY typeid DESC, brand DESC, encapsulation DESC, area DESC ), * FROM Product ) SELECT Spec, typeid, brand, encapsulation, area FROM t WHERE rn = 1 --删除重复数据: ;WITH t AS ( SELECT rn = ROW_NUMBER()OVER( PARTITION BY spec ORDER BY typeid DESC, brand DESC, encapsulation DESC, area DESC ), * FROM Product ) DELETE t WHERE rn > 1
from Product
group by Spec,typeid,brand,encapsulation,area
select Spec,typeid,brand,encapsulation,area,dense_rank()
over(partition by Spec,typeid,brand,encapsulation,area
order by Spec,typeid,brand,encapsulation,area desc ) igroup
from Product) PD
where PD.igroup > 1
同时也可以distinct 记录导出表,再导回。
;WITH t AS
(
SELECT rn = ROW_NUMBER()OVER(
PARTITION BY spec ORDER BY typeid DESC, brand DESC, encapsulation DESC, area DESC
), *
FROM Product
)
SELECT Spec, typeid, brand, encapsulation, area
FROM t
WHERE rn = 1 --删除重复数据:
;WITH t AS
(
SELECT rn = ROW_NUMBER()OVER(
PARTITION BY spec ORDER BY typeid DESC, brand DESC, encapsulation DESC, area DESC
), *
FROM Product
)
DELETE t
WHERE rn > 1