#oracle--Delete the sql statement with the id as the primary key, and leave only the first record in the duplicate record.

This question is a written test question for a financial software company. : The following table is a user table, and uerid is the primary key, as shown below. 这里写图片描述 要求能够通过一个sql语句删除所有重复的记录,并只留下重复记录中第一条记录的sql语句。

回答:

delete from userinfo u3 where u3.userid in (               
  with aaa as(select u2.userid,u2.age,u2.email,u2.password,u2.sex,u2.tel,u2.username from userinfo u2,
  (select count(0),
      u.age,
      u.email,
      u.password,
      u.sex,
      u.username,
      u.tel
  from userinfo u
  group by u.age,
         u.email,
         u.password,
         u.sex,
         u.username,
         u.tel
  having count(0) > 1) c
  where u2.age = c.age
  and u2.email = c.email
  and u2.password = c.password
  and u2.sex = c.sex
  and u2.tel = c.tel
  and u2.username = c.username)
  select aaa.userid from aaa where aaa.userid not in( 
    select min(a.userid) from aaa a
    group by a.age,
           a.email,
           a.password,
           a.sex,
           a.username,
           a.tel             
   )     
)

分析: The first step: To delete duplicate records, we must first find out which records are duplicates:

select count(0),
      u.age,
      u.email,
      u.password,
      u.sex,
      u.username,
      u.tel
  from userinfo u
  group by u.age,
         u.email,
         u.password,
         u.sex,
         u.username,
         u.tel
  having count(0) > 1

第二步: Find all duplicate records in the userinfo table:

select u2.userid,u2.age,u2.email,u2.password,u2.sex,u2.tel,u2.username from userinfo u2,
  (select count(0),
      u.age,
      u.email,
      u.password,
      u.sex,
      u.username,
      u.tel
  from userinfo u
  group by u.age,
         u.email,
         u.password,
         u.sex,
         u.username,
         u.tel
  having count(0) > 1) c
  where u2.age = c.age
  and u2.email = c.email
  and u2.password = c.password
  and u2.sex = c.sex
  and u2.tel = c.tel
  and u2.username = c.username

第步: Find the first record (the smallest userid) for the fields other than the primary key by grouping: Select min(a.userid) from aaa a Group by a.age,        A.email,        A.password,        A.sex,        A.username,        A.tel The record in the third step is removed from the duplicate record, and the remaining record is the duplicate record that needs to be deleted. The final statement is as follows.

delete from userinfo u3 where u3.userid in (               
  with aaa as(select u2.userid,u2.age,u2.email,u2.password,u2.sex,u2.tel,u2.username from userinfo u2,
  (select count(0),
      u.age,
      u.email,
      u.password,
      u.sex,
      u.username,
      u.tel
  from userinfo u
  group by u.age,
         u.email,
         u.password,
         u.sex,
         u.username,
         u.tel
  having count(0) > 1) c
  where u2.age = c.age
  and u2.email = c.email
  and u2.password = c.password
  and u2.sex = c.sex
  and u2.tel = c.tel
  and u2.username = c.username)
  select aaa.userid from aaa where aaa.userid not in( 
    select min(a.userid) from aaa a
    group by a.age,
           a.email,
           a.password,
           a.sex,
           a.username,
           a.tel             
   )     
)