场景如下:第一要根据email分组,然后查询出他的status在1,2,3,4中的记录个数大于等于2的记录。(1,2,3,4为有效)
t_user
id email status
1 [email protected] 1
2 [email protected] 2
3 [email protected] 3
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
8 [email protected] 5
9 [email protected] 5
10 [email protected] 1
11 [email protected] 1
12 [email protected] 4
如何写一个sql语句将t_user变成如下
id email status
1 [email protected] 1
2 [email protected] 2
3 [email protected] 3
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
11 [email protected] 1
12 [email protected] 4
t_user
id email status
1 [email protected] 1
2 [email protected] 2
3 [email protected] 3
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
8 [email protected] 5
9 [email protected] 5
10 [email protected] 1
11 [email protected] 1
12 [email protected] 4
如何写一个sql语句将t_user变成如下
id email status
1 [email protected] 1
2 [email protected] 2
3 [email protected] 3
4 [email protected] 1
5 [email protected] 2
6 [email protected] 2
7 [email protected] 3
11 [email protected] 1
12 [email protected] 4
解决方案 »
- 关于SQL语句的强弱选择性
- 表空间回收
- ORA-02291: 违反完整约束条件 (INTERLIB.FK_SER_BIND_REFERENCE_BIBLIOS) - 未找到父项关键字
- Oracle怎样获取某个表的建表语句?
- 我现在要重新安装一个WIN2000SERVER,想把整个ORACEL8i导出来,怎么办?请高手指点一下~!
- 另一个关于oracle备份的问题
- http://bbs.enetsky.com
- distinct与group by?
- 服务器重启之后,TNSListener服务需要再重启一次,才能打开数据库
- 数据库字段怎么会自己变汉字
- 一个存储过程执行速度的问题............
- 怎么判断某个字段的开头字母是P?
select 1 id ,'[email protected]' email, 1 status from dual
union all select 2 ,'[email protected]', 2 from dual
union all select 3 ,'[email protected]', 3 from dual
union all select 4 ,'[email protected]', 1 from dual
union all select 5 ,'[email protected]', 2 from dual
union all select 6 ,'[email protected]', 2 from dual
union all select 7 ,'[email protected]', 3 from dual
union all select 8 ,'[email protected]', 5 from dual
union all select 9 ,'[email protected]', 5 from dual
union all select 10 ,'[email protected]', 1 from dual
union all select 11 ,'[email protected]', 1 from dual
union all select 12 ,'[email protected]', 4 from dual
)
select * from t where email in (select email from t where status<=4 group by email having count(*)>=2 ) and status<=4
order by id
count(email) over(partition by emial order by status) count_email
from tbl_test
where email in(
select email from tbl_test where status<=4 group by email having count(*)>=2 )
and status status <=4;
select id ,email ,status, sum(1)over(partition by email order by id ) rows_sum
from t_user
where status<=4)
where rows_sum>=2