有个表 tab 字段 day_id,type_id
20110701 a
20110701 b
20110701 c
20110702 a
20110702 b
20110702 d
20110703 a
20110703 d
20110704 b
20110704 d求连续3天都出现的type_id,比如1号、2号、3号都出现了a,则a是符合的,如果1号、2号、4号出现a。则a是不符合的。求解!!!!!!!
20110701 a
20110701 b
20110701 c
20110702 a
20110702 b
20110702 d
20110703 a
20110703 d
20110704 b
20110704 d求连续3天都出现的type_id,比如1号、2号、3号都出现了a,则a是符合的,如果1号、2号、4号出现a。则a是不符合的。求解!!!!!!!
解决方案 »
- oracle sql语句
- 菜鸟求助oracle的简单查询语句
- 【急】Oracle 转Mysql
- Win2008+Oracle11g双机热备下Oracle如何安装
- 大家帮忙看一下Java oracle 数据库连接问题
- 求SQL精句,问过好多高手,都解决不了!!
- 初学oracle,一大堆基本概念问题。
- 怎样在JDBC中调用Oracle的一个存储过程,并且此存储过程返回一个结果集,放在ResultSet中?
- SYS登陆,已建一表A,一用户U,已给权限CONNECT,CREATE SESSION,想授权SELECT ON A TO U,出错权限不够,为什么?
- Access数据库中的关系设置在oracel怎么做呀
- ORACLE自增列怎么写?
- 若若一问:找出哪些人不是经理人
from (
select day_id,type_id,lead(day_id)over(partition by type_id order by day_id)-day_id as cnt
from tb)
group by type_id
having count(cnt)=3
from(
select day_id, type_id, to_date(day_id, 'YYYYMMDD') - dense_rank() over(partition by type_id
order by day_id) flag
from tab)
group by type_id, flag
having count(distinct day_id) >= 3;
SQL> WITH t AS (
2 SELECT to_date('20110701') day_id,'a' type_id FROM DUAL UNION ALL
3 SELECT to_date('20110701') day_id,'b' type_id FROM DUAL UNION ALL
4 SELECT to_date('20110701') day_id,'c' type_id FROM DUAL UNION ALL
5 SELECT to_date('20110702') day_id,'a' type_id FROM DUAL UNION ALL
6 SELECT to_date('20110702') day_id,'b' type_id FROM DUAL UNION ALL
7 SELECT to_date('20110702') day_id,'d' type_id FROM DUAL UNION ALL
8 SELECT to_date('20110703') day_id,'a' type_id FROM DUAL UNION ALL
9 SELECT to_date('20110703') day_id,'d' type_id FROM DUAL UNION ALL
10 SELECT to_date('20110704') day_id,'b' type_id FROM DUAL UNION ALL
11 SELECT to_date('20110704') day_id,'d' type_id FROM DUAL
12 )
13 SELECT m.type_id
14 FROM (SELECT t.type_id,
15 COUNT(*) OVER(PARTITION BY type_id ORDER BY day_id RANGE BETWEEN CURRENT ROW AND INTERVAL '2' DAY following) cnt
16 FROM t) m
17 WHERE m.cnt = 3
18 ;TYPE_ID
-------
a
d
SELECT T1.type_id
FROM tab T1, tab T2, tab T3
WHERE T1.type_id = T2.type_id
AND T1.day_id + 1 = T2.day_id
AND T1.type_id = T3.type_id
AND T1.day_id + 2 = T3.day_id
GROUP BY T1.type_id
to_date('20110701','yyyymmdd')这个原因?