select b.deptname 地区,sum(a.session_id) "03-01" from login a,user_manager b where
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')=to_char(to_date('01-3月 -08','dd-month-yy'),'yyyy-mm-dd') group by b.deptname表login记录用户登陆信息,表user_manager记录用户信息.现在要求根据分地区统计2008-3-1到2008-3-31用户登陆次数.
以上sql只能求出2008-3-1一天的登陆统计.请问怎么才能得到一段日期间的统计呢?
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')
between to_char(to_date('01-3月 -08','dd-month-yy'),'yyyy-mm-dd')
and to_char(to_date('31-3月 -08','dd-month-yy'),'yyyy-mm-dd') group by b.deptname
from login a,user_manager b
where (a.userid = b.userid)
and a.userid in
(select userid from user_manager
where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')>=to_char(to_date('01-3月 -08','dd-month-yy'),'yyyy-mm-dd')
and to_char(a.logon_time,'yyyy-mm-dd')<=to_char(last_day(to_date('01-3月 -08','dd-month-yy')),'yyyy-mm-dd')
group by b.deptname,substr(logon_time,1,8)我没测试,你看这样可以吗
from login a,user_manager b
where a.userid = b.userid and a.userid in (select userid from user_manager where USER_FLAG =11) and to_char(a.logon_time,'yyyy-mm-dd') between to_char(to_date('01-3月 -08','dd-month-yy'),'yyyy-mm-dd')
and to_char(to_date('31-3月 -08','dd-month-yy'),'yyyy-mm-dd')
group by a.logon_time,b.deptname 地区
A 12 33 35 23 23
B 11 37 39 33 25
C 22 36 31 24 45
D 13 37 35 23 23
. ........................................
. ........................................
. ........................................
A 12 33 35 23 23
B 11 37 39 33 25
C 22 36 31 24 45
D 13 37 35 23 23
. ........................................
. ........................................
. ........................................ 你想这样的,写的语句看起来会很麻烦的select b.deptname 地区,
sum(decode(to_char(a.logon_time,'mm-dd'),'03-01',a.session_id,0)) "03-01",
sum(decode(to_char(a.logon_time,'mm-dd'),'03-02',a.session_id,0)) "03-02",
.
.
......
sum(decode(to_char(a.logon_time,'mm-dd'),'03-31',a.session_id,0)) "03-31",
from login a,user_manager b where
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')>='2008-03-01'
and to_char(a.logon_time,'yyyy-mm-dd')<='2008-03-31' group by b.deptname其实为了通用性,最好是这样的select b.deptname 地区,
sum(decode(to_char(a.logon_time,'dd'),'01',a.session_id,0)) "01",
sum(decode(to_char(a.logon_time,'dd'),'02',a.session_id,0)) "02",
.
.
......
sum(decode(to_char(a.logon_time,'dd'),'31',a.session_id,0)) "31",
from login a,user_manager b where
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')>='2008-03-01'
and to_char(a.logon_time,'yyyy-mm-dd')<='2008-03-31' group by b.deptname
A 12 33 35 23 23
B 11 37 39 33 25
C 22 36 31 24 45
D 13 37 35 23 23
. ........................................
. ........................................
. ........................................ 你想这样的,写的语句看起来会很麻烦的
select b.deptname 地区,
sum(decode(to_char(a.logon_time,'mm-dd'),'03-01',a.session_id,0)) "03-01",
sum(decode(to_char(a.logon_time,'mm-dd'),'03-02',a.session_id,0)) "03-02",
.
.
......
sum(decode(to_char(a.logon_time,'mm-dd'),'03-31',a.session_id,0)) "03-31",
from login a,user_manager b where
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')>='2008-03-01'
and to_char(a.logon_time,'yyyy-mm-dd') <='2008-03-31' group by b.deptname 其实为了通用性,最好是这样的
select b.deptname 地区,
sum(decode(to_char(a.logon_time,'dd'),'01',a.session_id,0)) "01",
sum(decode(to_char(a.logon_time,'dd'),'02',a.session_id,0)) "02",
.
.
......
sum(decode(to_char(a.logon_time,'dd'),'31',a.session_id,0)) "31",
from login a,user_manager b where
(a.userid = b.userid)
and a.userid in (select userid from user_manager where USER_FLAG =11)
and to_char(a.logon_time,'yyyy-mm-dd')>='2008-03-01'
and to_char(a.logon_time,'yyyy-mm-dd') <='2008-03-31' group by b.deptname
to_char(a.logon_time,'yyyy-mm-dd')=to_char(to_date('01-3月 -08','dd-month-yy'),'yyyy-mm-dd')
logon_time已经转成char了,后面的条件直接写字符串的日期就行了,除非你是传参数进来的,而传的参数又是'01-3月 -08'这种格式的,其实也没关系
可以这样
to_char(a.logon_time,'dd-month-yy')='01-3月 -08'
反正不需要选把字符串转成DATE然后再转成char,多麻烦啊
小弟还有一问:为什么"sum(decode(to_char(a.logon_time,'dd'),'01',a.session_id,0)) "01",....."这种方式的通用性更好呢?
否则'mm-dd'有月,每个月都需要手工改月份
已经帮解释了
最好现在语句里加个月份的字段,再group by 月
就更好了,这样在输入日期时就不用考虑跨月了
或者意义是输入个基准日,让系统算起始和截止日期
比如
and to_char(a.logon_time,'yyyy-mm-dd')>=to_char(sysdate,'yyyy-mm')||'-01'
and to_char(a.logon_time,'yyyy-mm-dd') <=to_char(last_day(sysdate),'yyyy-mm-dd')这里我假设的输入参数是日期型的,你可以把sysdate换掉
如果是字符型的,可以这样,假设输入的日期格式是'yyyy-mm-dd'
and to_char(a.logon_time,'yyyy-mm-dd')>=substr(input_date,1,7)||'-01'
and to_char(a.logon_time,'yyyy-mm-dd') <=to_char(last_day(to_date(input_date,'yyyy-mm-dd')),'yyyy-mm-dd')