sql带条件的重复数据查询

数据库：sql service
员工表：yg
yg字段：email（邮箱），ygtype（是否在职：0：在；1：离职）
现在要查询邮箱重复的在职的员工sql怎么写

select * from yq a where (a.email) in (select email from yq group by email having count(*) > 1) and a.yqtype = 0

希望对你有用

 SELECT
    ygtype,email, COUNT(*) AS CountOf
    FROM yg
    GROUP BY ygtype,email
    HAVING COUNT(*)>1

要是需要得到员工的ID，用下面：

 SELECT
    yg.id,yg.email,yg.ygtype
    FROM yg y
        INNER JOIN (SELECT
                        email,ygtype COUNT(*) AS CountOf
                        FROM yg
                        GROUP BY email,ygtype
                        HAVING COUNT(*)>1
                    ) dt ON y.email=dt.email and y.ygtype=dt.ygtype

不行啊，现在我需要的是查出在职的人中有哪些邮箱重复了，但用你的查出来的数据不对@战在春秋

select ,count() from yg where ygtype=1 group by email having count(email)>1