常用sql语句集锦

1. 检查重复记录

   select count(f_uid) num, f_uid from t_crm_user 
   group by f_uid having count(f_uid) > 1
   order by num desc
   

2. 删除重复记录并保留id最大的一条记录

   DELETE FROM t_crm_user where f_uid IN (
       SELECT f_uid from (
           SELECT f_uid FROM t_crm_user GROUP BY f_uid HAVING count(f_uid) > 1
       ) a
   ) AND id NOT IN (
       SELECT keepId FROM (
           SELECT max(id) keepId FROM t_crm_user GROUP BY f_uid HAVING count(f_uid) > 1
       ) b
   )
   

3. 获取分组后每组的前三条数据
   Mysql

   -- 效率比较慢
   select a.* 
   from
   (
   select t1.*,(select count(*)+1 from 表 where 分组字段=t1.分组字段 and 排序字段<t1.排序字段) as group_id
   from 表 t1
   ) a
   where a.group_id<=3
   

   Oracle

   SELECT t.*         
      FROM (SELECT ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC) rn,         
            b.*         
            FROM 表 b) t         
     WHERE t.rn <= 3  ;