跪求一条查询重复记录的sql语句
场景如下：第一要根据email分组，然后查询出他的status在1，2，3，4中的记录个数大于等于2的记录。（1，2，3，4为有效）
t_user
id email status
1 aa@gmail.com 1
2 aa@gmail.com 2
3 aa@gmail.com 3
4 yy@gmail.com 1
5 yy@gmail.com 2
6 zz@gmail.com 2
7 zz@gmail.com 3
8 zz@gmail.com 5
9 xx@gmail.com 5
10 xy@gmail.com 1
11 qx@gmail.com 1
12 qx@gmail.com 4
如何写一个sql语句将t_user变成如下
id email status
1 aa@gmail.com 1
2 aa@gmail.com 2
3 aa@gmail.com 3
4 yy@gmail.com 1
5 yy@gmail.com 2
6 zz@gmail.com 2
7 zz@gmail.com 3
11 qx@gmail.com 1
12 qx@gmail.com 4

[解决办法]

SQL code

with t as ( select  1 id ,'aa@gmail.com' email, 1 status from dual union all select  2 ,'aa@gmail.com', 2 from dual union all select  3 ,'aa@gmail.com', 3 from dual union all select  4 ,'yy@gmail.com', 1 from dual union all select  5 ,'yy@gmail.com', 2 from dual union all select  6 ,'zz@gmail.com', 2 from dual union all select  7 ,'zz@gmail.com', 3 from dual union all select  8 ,'zz@gmail.com', 5 from dual union all select  9 ,'xx@gmail.com', 5 from dual union all select 10 ,'xy@gmail.com', 1 from dual union all select 11 ,'qx@gmail.com', 1 from dual union all select 12 ,'qx@gmail.com', 4 from dual)select  * from t where email  in (select email from t where status<=4 group by email having count(*)>=2 ) and status<=4order by id