mysql - 如何删除mysql中不重复的行？

Question

我正在尝试显示有多个受抚养人的所有员工的 ssn、名字 (fname)、姓氏 (lname)、受抚养人姓名、受抚养人性别和受抚养人关系。我能够获取所有受抚养人的列表。显示具有多个受抚养人的条目的查询是什么？

显示创建表员工

Employee | CREATE TABLE `employee` (
`fname` varchar(15) NOT NULL,
`minit` varchar(1) DEFAULT NULL,
`lname` varchar(15) NOT NULL,
`ssn` char(9) NOT NULL DEFAULT '',
`bdate` date DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`superssn` char(9) DEFAULT NULL,
`dno` int(11) DEFAULT NULL,
PRIMARY KEY (`ssn`),
KEY `fk_employee_department` (`dno`),
KEY `fk_employee_employee` (`superssn`),
CONSTRAINT `fk_employee_department` 
           FOREIGN KEY (`dno`) REFERENCES `department` (`dnumber`),
CONSTRAINT `fk_employee_employee` 
           FOREIGN KEY (`superssn`) REFERENCES `employee` (`ssn`)
ENGINE=InnoDB DEFAULT CHARSET=latin1 |

显示创建表相关

    DEPENDENT | CREATE TABLE `dependent` (
 `essn` char(9) NOT NULL DEFAULT '',
 `dependent_name` varchar(15) NOT NULL DEFAULT '',
 `sex` char(1) DEFAULT NULL,
 `bdate` date DEFAULT NULL,
 `relationship` varchar(8) DEFAULT NULL,
 PRIMARY KEY (`essn`,`dependent_name`),
 CONSTRAINT `fk_dependent_employee` 
             FOREIGN KEY (`essn`) REFERENCES `employee` (`ssn`)
 ENGINE=InnoDB DEFAULT CHARSET=latin1 |

当前使用的查询:

select e. ssn,e.fname,e.lname,d.dependent_name,d.sex,d.relationship 
from dependent AS d ,Employee as e
WHERE e.ssn=d.essn

Answer 1

使用子查询来计算每个子查询的依赖项数量并将其加入。这将获得您想要的过滤器:

select e.ssn, e.fname, e.lname, d.dependent_name, d.sex, d.relationship 
from Employee e join
     dependent d 
     on e.ssn = d.essn join
     (select d.essn, count(*) as cnt
      from dependent d
      group by d.essn
      having cnt >= 2
     ) d2
     on e.ssn = d2.essn
order by e.ssn;