场景如下：第一要根据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