解决方案 »
- 新手求教
- 一个关于session的数据库问题,请教!
- 够郁闷的,PLSQL Developer,SQL Plus,em连接都正常!!
- 关于pl/sql几个问题
- pl sql 小查询,路过的不要错过了
- 在window2000装的oracle9i服务器,Start HTTP Server powered by Apache失败?
- 求救:ManagementServer服务无法启动
- 192的内存,P3 CPU专门用于ORACLE服务器,SGA的大小设多少最为合适?
- 在我安装server端后,实例oral 表空间ttdata 使用net8 config.. 指定service 出现问题,怎么也联不上啊!!!
- 请问什么快捷键保存并关闭text editor?
- 如何修改归档日志文件的保存路径?
- 求个思路 关于分组函数的 sql 在线等!
select 'jack' account,to_date('2011-01-01','yyyy-mm-dd') dt from dual
union all
select 'jack' account,to_date('2011-01-02','yyyy-mm-dd') dt from dual
union all
select 'jack' account,to_date('2011-02-01','yyyy-mm-dd') dt from dual
)select t.account,t.dt,count(to_char(t.dt,'yyyy-mm')) over(partition by to_char(t.dt,'yyyy-mm'))
from t
from t
(SELECT 'jack' ACCOUNT,to_date('2011-01-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-01-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-03','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-02','yyyy-mm-dd') LoginDate FROM dual)
SELECT ACCOUNT,LoginDate,COUNT(1) over(PARTITION BY FLAG) num FROM
(SELECT ACCOUNT,
LoginDate,
LoginDate - DENSE_RANK() OVER(PARTITION BY ACCOUNT ORDER BY LoginDate) FLAG
FROM t);
jack 2011-01-01 2
jack 2011-01-02 2
jack 2011-02-01 3
jack 2011-02-02 3
jack 2011-02-03 3jack 在 2011-01-01,2011-01-02, 2011-02-01, 2011-02-02, 2011-02-03 这5天里有登录,
其中
2011-01-01,2011-01-02 这 2天是连续的,就标明为 2
2011-02-01, 2011-02-02, 2011-02-03 这 3天是连续的,就标明为 3.
(SELECT 'jack' ACCOUNT,to_date('2011-01-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-01-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-01-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-03','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-02','yyyy-mm-dd') LoginDate FROM dual)
SELECT ACCOUNT,LoginDate,COUNT(1) over(PARTITION BY FLAG) num FROM
(SELECT ACCOUNT,
LoginDate,
LoginDate - DENSE_RANK() OVER(PARTITION BY ACCOUNT ORDER BY LoginDate) FLAG
FROM (SELECT DISTINCT ACCOUNT,LoginDate FROM T));
(2)WITH t AS
(SELECT 'jack' ACCOUNT,to_date('2011-01-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-01-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-01-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-02','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'jack' ACCOUNT,to_date('2011-02-03','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-01','yyyy-mm-dd') LoginDate FROM dual
UNION ALL SELECT 'mary' ACCOUNT,to_date('2011-03-02','yyyy-mm-dd') LoginDate FROM dual)
SELECT ACCOUNT,LoginDate,COUNT(1) over(PARTITION BY LoginDate-ROWNUM) num FROM
(SELECT DISTINCT ACCOUNT,LoginDate FROM t ORDER BY ACCOUNT,LoginDate)