A classic sql interview question! I think I have a good time playing with sql!

冰点飞扬

Student ID (Automatic Numbering) Name Gender Age

0001 xw male 18

0002 mc female 16

0003 ww male 21

0004 xw male 18

Please write SQL statements that implement the following functions:
Delete redundant records with the same fields except the student number (automatic number) field!

jieslie

Method one, delete table where student number (automatic numbering) not in
(select max (student number (automatic number)) as xh, name, gender, age
  from table
group by name, gender, age) A
Method Two,
delete table where student number (automatic numbering) not in (
select max (a. student number) from table B, (select distinct name, gender, age from table) A
 where A. name = b.name and a.sex = b.sex and a.age = b.age
) C

The above method does not know whether it is possible, and has not been tested in SQL.

azuazuazu

delete from tablename where student number (automatic numbering) notexists (select distinct name, gender, age from tablename)

402374565

up,azuazuazupositive solution!

zzclark

azuazuazu, there is no distinction between in and exists ...

The idea of ​​jieslieis correct, and the wording after debugging is as follows: (note that the sub query of in can only select one column)
DELETE FROM table1
WHERE (Student ID NOT IN
          (SELECT MAX (student number) AS xh
         FROM TABLE1
         GROUP BY name, gender, age))

kislvens

Positive Solutions Upstairs

levy_ngacn

What is classic?

jsj_yy

Basic.

kcactus

DELETE FROM Test2
where [学 号] in
(select [Student ID] = min ([Student ID]) from Test2 group by [Name], [Gender], [Age] having count (*)> 1)

fleetadmiral

kcactus

This is definitely no problem. The idea is clear.