如何使用SQL查询从表中删除重复项 [英] How to remove duplicates from table using SQL query

问题描述

我有一个表格如下:

emp_name   emp_address  sex  matial_status  
uuuu       eee          m    s
iiii       iii          f    s
uuuu       eee          m    s

我想删除基于 3 个字段 emp_name、emp_address 和 sex 的重复条目.我的结果表(删除重复项后)应该看起来像 -

I want to remove the duplicate entries based on 3 fields emp_name, emp_address and sex. and my resultant table (after removing the duplicates) should look like -

emp_name    emp_address   sex   marital_status
uuuu        eee           m     s
iiii        iii           f     s

我不记得如何为此编写 SQL 查询.有人请帮忙吗?

I am not able to recall how to write a SQL Query for this. an anyone pls help?

推荐答案

看起来所有四列值都是重复的，所以你可以这样做 -

It looks like all four column values are duplicated so you can do this -

select distinct emp_name, emp_address, sex, marital_status
from YourTable

但是，如果婚姻状况可能不同，并且您有其他一些列可供选择(例如，您想要基于列 create_date 的最新记录)，您可以执行此操作

However if marital status can be different and you have some other column based on which to choose (for eg you want latest record based on a column create_date) you can do this

select emp_name, emp_address, sex, marital_status
from YourTable a
where not exists (select 1 
                   from YourTable b
                  where b.emp_name = a.emp_name and
                        b.emp_address = a.emp_address and
                        b.sex = a.sex and
                        b.create_date >= a.create_date)

这篇关于如何使用SQL查询从表中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！