常用SQL_把排序的字段作为特定的字段展示_重复数据查询 删除

把排序的字段作为特定的字段展示

postgresql

select rank() OVER (order by index_value desc),* from dr_regional_dependence_index where quarter='2022-Q1' and index_class='1' order by index_value desc

查询重复数据
select class_code,count(*) from dr_lybase group by class_code having count(class_code)>1;

删除重复数据:
DELETE from dr_lybase where ctid in( select min(ctid) from dr_lybase group by class_code having count(class_code)>1 )

/查询2022年中的指数重复数据*/
select releasedate,class_code,count(*) from liny_price_index_class_week2022 group by class_code,releasedate having count(class_code)>1;

/*删除2022年中的数据/
DELETE from liny_price_index_class_week2022 where ctid in(select min(ctid) from liny_price_index_class_week2022 group by class_code,releasedate having count(class_code)>1)

mysql

select count(1) from bs_company

DELETE FROM bs_company WHERE com_uscc IN(

SELECT com_uscc FROM (SELECT com_uscc FROM bs_company GROUP BY com_uscc HAVING COUNT(com_uscc) > 1) table1)AND com_id NOT IN (
SELECT id FROM (SELECT MIN(com_id) AS id FROM bs_company GROUP BY com_uscc HAVING COUNT(com_uscc) > 1) table2)

参考:https://blog.csdn.net/qq_39236157/article/details/124290085