ORACLE和MYSQL 去重查询

ORACLE

// 去重查询
select * from(
　　select 别名1.*, row_number() over(partition by 别名1.列名1 order by 别名1.列名2 ASC) 别名2 from 表名 别名1 ) where 别名2 = 1;

例：select * from(
　　　　select a.*, row_number() over(partition by a.user_id order by a.date ASC, a.name DESC) su from user a ) where su = 1;

// 查询重复数据
SELECT * FROM 表名 WHERE 列名1 IN (SELECT 列名1 FROM 表名 GROUP BY 列名1 HAVING COUNT (列名1) >1)

例：SELECT * FROM hospital_info WHERE lic_number IN (SELECT lic_number FROM hospital_info GROUP BY lic_number HAVING COUNT (lic_number) >1)

 

MYSQL

// 去重查询
select 列名1 from 表名 GROUP BY 列名1;

例：select user_id from user GROUP BY user_id;

// 查询重复数据
select 列名1, count(*) as count from 表名 group by 列名1 having count > 1;

例：select user_id, count(*) as count from user group by user_id having count > 1;