mysql - 带计数的子选择，SQL 查询

Question

您好，我需要选择他们部门中至少有两名男性和女性的薪水相同的所有女工的员工号和薪水..

这是表格

EMP:
empno|ename|deptno|sal|gender   
DEPT:
deptno|dname

这是我的代码，出于某种原因，这个剂量给出了所需的结果

SELECT *
FROM EMP E
WHERE E.GENDER = 'F' AND 2 <= (SELECT COUNT(*)
                               FROM EMP E2
                               WHERE E2.GENDER = 'M' AND
                               E2.SAL = E.SAL
                               AND E.DEPTNO = E2.DEPTNO);

Answer 1

select e1.* 
from emp e1
join
(
    select sal, deptno
    from emp
    group by sal, deptno
    having count(distinct gender) = 2
    and sum(gender = 'M') >= 2
) e2 on e1.sal = e2.sal and e1.deptno = e2.deptno
where e1.gender = 'F'

SQLFiddle demo