表结构
sfzh ,sj,qh
0001,20130101,010
0001,20120101,020想求出比如2013-01-10至2013-03-01期间一周内去过三个地方的人大牛支持,谢谢
sfzh ,sj,qh
0001,20130101,010
0001,20120101,020想求出比如2013-01-10至2013-03-01期间一周内去过三个地方的人大牛支持,谢谢
解决方案 »
- 这个sql怎么写,我想把他导入oracle数据库中
- session的问题,请大大们帮忙
- 安装oracle 10g rac挂载文件系统的时候出错!
- 高分求解:怎样才能用 select 从游标变量中取得结果集
- oracle问题一大堆,又是初学者,很多问题查以前的记录也查不到,希望各位教一下
- oracle 中文不能识别问题
- 如何复制大量记录,只是主键编码改一下,其中某一个字段也相应改编
- tcp端口只开放80,1521,7778,3334,66,为什么oracle客户端连接不了?
- 还是oracle中日期与时间的问题 各位多多指教 谢谢! (急!急!急!急!急!急!)
- Oracle数据库权限收集问题
- 还是昨天的union链接问题,××××在线等待×××××
- 请问这样的sql语句怎么写法
with t1 as
(
select '0001' sf,date'2013-01-01' sj,'010' qh from dual union all
select '0002' sf,date'2013-01-01' sj,'020' qh from dual union all
select '0001' sf,date'2013-01-16' sj,'010' qh from dual union all
select '0001' sf,date'2013-01-17' sj,'020' qh from dual union all
select '0001' sf,date'2013-01-19' sj,'030' qh from dual union all
select '0002' sf,date'2013-01-20' sj,'010' qh from dual union all
select '0002' sf,date'2013-01-21' sj,'010' qh from dual union all
select '0002' sf,date'2013-01-21' sj,'020' qh from dual union all
select '0001' sf,date'2013-01-22' sj,'010' qh from dual union all
select '0001' sf,date'2013-01-27' sj,'010' qh from dual union all
select '0001' sf,date'2013-01-30' sj,'010' qh from dual
)select sf "编号",ww "周数",c_num "次数"
from
(
select sf,to_char(sj,'ww') ww,count(distinct qh) c_num
from t1
where sj >= date'2013-01-01' and sj <= date'2013-01-31'
group by sf,to_char(sj,'ww')
)
where c_num >= 3
编号 周数 次数
---------------------------------
1 0001 03 3
select t1.*
from t1,
(
select sf,to_char(sj,'ww') ww,count(distinct qh) c_num
from t1
where sj >= date'2013-01-01' and sj <= date'2013-01-31'
group by sf,to_char(sj,'ww')
) t2
where t1.sf = t2.sf and t2.c_num >= 3 and to_char(t1.sj,'ww') = ww
sf sj qh
--------------------------------------
1 0001 2013/1/16 010
2 0001 2013/1/17 020
3 0001 2013/1/19 030
with
t as( select '0001' sfzh ,'20130111' sj,'001' qh from dual
union all
select '0001' sfzh,'20130113' sj,'002' qh from dual
union all
select '0001' sfzh,'20130114' sj,'004' qh from dual
union all
select '0001' sfzh,'20130115' sj ,'001' qh from dual
union all
select '0001' sfzh,'20130116' sj ,'003' qh from dual
union all
select '0001' sfzh,'20130119' sj ,'001' qh from dual
union all
select '0001' sfzh,'20130126' sj ,'003' qh from dual
union all
select '0004' sfzh,'20130101' sj,'001' qh from dual
union all
select '0002' sfzh,'20130201' sj,'001' qh from dual
union all
select '0001' sfzh,'20130401' sj,'001' qh from dual
union all
select '0002' sfzh,'20130111' sj,'001' qh from dual
union all
select '0002' sfzh,'20130221' sj,'001' qh from dual
union all
select '0003' sfzh,'20130201' sj,'001' qh from dual),
t1 as (select sfzh,sj,qh,decode(row_number()over(partition by sfzh,qh order by sfzh asc,sj asc,qh asc),1,1,0)rn
from t
where t.sj between '20130110' and '20130301'
),
t2 as (select sfzh,sum(rn)over(partition by sfzh order by sfzh ,sj ,qh
rows between 7 preceding and 0 following)cur_7days_qh
from t1)
select distinct t2.sfzh
from t2
where t2.cur_7days_qh>=3;
with
t as( select '0001' sfzh ,date'2013-01-11' sj,'001' qh from dual
union all
select '0001' sfzh,date'2013-01-13' sj,'002' qh from dual
union all
select '0001' sfzh,date'2013-01-12' sj,'001' qh from dual
union all
select '0001' sfzh,date'2013-01-14' sj,'002' qh from dual
union all
select '0001' sfzh,date'2013-01-15' sj ,'001' qh from dual
union all
select '0001' sfzh,date'2013-01-16' sj ,'002' qh from dual
union all
select '0001' sfzh,date'2013-01-20' sj ,'002' qh from dual
union all
select '0001' sfzh,date'2013-01-20' sj ,'001' qh from dual
union all
select '0001' sfzh,date'2013-01-26' sj ,'003' qh from dual
union all
select '0004' sfzh,date'2013-01-01' sj,'001' qh from dual
union all
select '0002' sfzh,date'2013-02-01' sj,'001' qh from dual
union all
select '0002' sfzh,date'2013-02-03' sj,'002' qh from dual
union all
select '0002' sfzh,date'2013-02-05' sj,'003' qh from dual
union all
select '0001' sfzh,date'2013-04-01' sj,'001' qh from dual
union all
select '0002' sfzh,date'2013-01-11' sj,'001' qh from dual
union all
select '0002' sfzh,date'2013-02-21' sj,'001' qh from dual
union all
select '0003' sfzh,date'2013-02-01' sj,'001' qh from dual),
t1 as (select sfzh,sj,qh,
first_value(sj)over(partition by sfzh order by sj range between interval '6' day preceding and interval '0' day following )begin_sj,
last_value(sj)over(partition by sfzh order by sj range between interval '6' day preceding and interval '0' day following )end_sj
,row_number()over(partition by sfzh order by sfzh,sj,qh)rn
from t
where t.sj between to_date('2013-01-10','yyyy-mm-dd') and to_date('2013-03-01','yyyy-mm-dd') ),
t2 as (select t.sfzh,t1.rn,t.qh from t,t1 where t.sfzh=t1.sfzh and t.sj between t1.begin_sj and t1.end_sj
group by t.sfzh,t1.rn,t.qh)
select distinct t2.sfzh
from t2
group by t2.sfzh,t2.rn
having count(*)>=3;