例如我的表是这样的
ID USERNAME LOGTIME WEEKDAY
1 SAM 2008-11-24 星期一
2 SAM 2008-11-25 星期二
3 SAM 2008-11-26 星期三
4 ROSE 2008-11-24 星期一
5 ROSE 2008-11-25 星期二是这样的,这个表是记录当用户登陆的时候,就往这个表插一条记录,这里的ROSE用户2008-11-26号没登陆,就表示缺勤了,就没有插到数据
如此类推
我现在想要的效果是这样,要查出所有用户每个月的缺勤次数,星期六和星期日的不算,然后在页面显示成这样序号 姓 名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 总数
ID USERNAME LOGTIME WEEKDAY
1 SAM 2008-11-24 星期一
2 SAM 2008-11-25 星期二
3 SAM 2008-11-26 星期三
4 ROSE 2008-11-24 星期一
5 ROSE 2008-11-25 星期二是这样的,这个表是记录当用户登陆的时候,就往这个表插一条记录,这里的ROSE用户2008-11-26号没登陆,就表示缺勤了,就没有插到数据
如此类推
我现在想要的效果是这样,要查出所有用户每个月的缺勤次数,星期六和星期日的不算,然后在页面显示成这样序号 姓 名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 总数
解决方案 »
- spool导出VARCHAR2(4000)的数据就换行了,linesize设置了貌似没有什么效果。急急急,求助。。
- oracle如何修改导出的数据及如何将文本格式的内容导入到oracle中
- alter database recover automatic standby datafile 3 until controlfile
- 求oracle两个存储过程
- SQL Server to Oracle 数据迁移 (新)
- 急求一sql查询语句
- 如何在存储过程中把 select count(*) from tb1的返回值赋给变量,在线等
- 关于游标作out参数的问题
- Oracle 8i安装好之后,默认Enterprise Manager 登陆账号和密码是什么?
- weblogic安装项目报错
- oracle存储过程求教
- 再咨询一条SQL语句
另外你要统计缺勤数就麻烦点,要把每月的上班天数给列出来
这分数我实在想要阿
但是 我不写出答案
我给你条思路
你愿意给我分数就给
不愿意的话 也算了
我之所以不给你答案 是希望你自己写写下边我说首先 如果大家都没有登录 那么就应该是放假或者星期天我是这么认为
首先
select distinct LOGTIME from tablename where 本月条件(这个条件自己写吧)这句话就是这个月上半的时间了select distinct USERNAME from tablename where 本月条件(这个条件自己写吧)这句话就是这个月上班的所有人了那么
SELECT * FROM
(
select distinct LOGTIME from tablename where 本月条件
),
(
select distinct USERNAME from tablename where 本月条件
)这句话就能表示出所有人这个月都应该上班的时间然后采用outer joinselect ANAME, LOGTIME from
(
select A.USERNAME ANAME, A.LOGTIME, B.USERNAME BNAME
from
(
SELECT * FROM
(
select distinct LOGTIME from tablename where 本月条件
),
(
select distinct USERNAME from tablename where 本月条件
)
) A, tablename B
where a.LOGTIME = B.LOGTIME(+) AND a.USERNAME = B.USERNAME (+)
)
where BNAME IS null就能知道谁什么时间没有来了道理是相通的 自己想想咯
你把我说的这个sql注意加工一下
很容易就满足需要了
原理 应该都一样 如果不明白的话
再问
SQL> SELECT * FROM KQB ORDER BY ID; -- 测试数据,此处制造了SAM是全勤 ID USERNAME LOGTIME WEEKDAY
--------------------------------------- ---------- ----------- ----------
1 SAM 2008-11-24 星期一
2 SAM 2008-11-25 星期二
3 SAM 2008-11-26 星期三
4 SAM 2008-11-27 星期四
5 SAM 2008-11-28 星期五
6 ROSE 2008-11-24 星期一
7 ROSE 2008-11-25 星期二7 rows selectedSQL>
SQL> SELECT ROWNUM AS "序号", T.*
2 FROM (SELECT USERNAME AS "姓名",
3 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '01', 1, 0)) AS "一月",
4 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '02', 1, 0)) AS "二月",
5 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '03', 1, 0)) AS "三月",
6 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '04', 1, 0)) AS "四月",
7 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '05', 1, 0)) AS "五月",
8 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '06', 1, 0)) AS "六月",
9 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '07', 1, 0)) AS "七月",
10 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '08', 1, 0)) AS "八月",
11 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '09', 1, 0)) AS "九月",
12 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '10', 1, 0)) AS "十月",
13 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '11', 1, 0)) AS "十一月",
14 SUM(DECODE(TO_CHAR(LOGTIME, 'mm'), '12', 1, 0)) AS "十二月",
15 COUNT(1) AS "总数"
16 FROM (SELECT *
17 FROM (SELECT A.USERNAME, B.LOGTIME, B.WEEKDAY
18 FROM (SELECT DISTINCT USERNAME
19 FROM KQB
20 ORDER BY USERNAME) A,
21 (SELECT DISTINCT LOGTIME, WEEKDAY
22 FROM KQB
23 WHERE WEEKDAY NOT IN ('星期六', '星期日')) B) C
24 WHERE NOT EXISTS (SELECT 1
25 FROM KQB
26 WHERE USERNAME = C.USERNAME
27 AND LOGTIME = C.LOGTIME
28 AND WEEKDAY = C.WEEKDAY))
29 GROUP BY USERNAME) T
30 /-- 输出结果 序号 姓名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 总数
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ROSE 0 0 0 0 0 0 0 0 0 0 3 0 3SQL>
SELECT A.USERNAME, B.LOGTIME, B.WEEKDAY
FROM (SELECT DISTINCT USERNAME
FROM KQB
ORDER BY USERNAME) A,
(SELECT DISTINCT LOGTIME, WEEKDAY
FROM KQB
WHERE WEEKDAY NOT IN ('星期六', '星期日')) B
这段SQL只能简单的列出上班天select *
from (
SELECT TRUNC (SYSDATE, 'yyyy') + ROWNUM - 1 days,
decode(TO_CHAR (TRUNC (SYSDATE, 'yyyy') + ROWNUM - 1, 'd') ,'1',1,'7',1,0) if_h_day
FROM all_objects
WHERE ROWNUM <=
TRUNC (ADD_MONTHS (SYSDATE, 12), 'yyyy')
- TRUNC (SYSDATE, 'yyyy'))
where if_h_day=0
and to_char(days,'mmdd') not in ('0101','0501','1001','1002','1003')
-- 弄了我半个小时...
SQL> SELECT * FROM TEST_DATE; ID USERNAME LOGTIME WEEKDAY
---------- -------------------- ----------- --------------------
1 SAM 2008-11-24 星期一
2 SAM 2008-11-25 星期二
3 SAM 2008-11-26 星期三
4 ROSE 2008-11-24 星期一
5 ROSE 2008-11-25 星期二SQL> SELECT SUBSTR(LOGDATE,0,4) "年份",
2 USERNAME "姓 名",
3 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'01',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "一月",
4 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'02',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "二月",
5 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'03',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "三月",
6 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'04',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "四月",
7 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'05',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "五月",
8 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'06',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "六月",
9 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'07',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "七月",
10 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'08',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "八月",
11 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'09',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "九月",
12 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'10',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "十月",
13 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'11',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "十一月",
14 MAX(DECODE(SUM_DATE,DECODE(SUBSTR(LOGDATE,5,6),'12',LOGDATE,0),COUNTS-WORK_COUNTS,0)) "十二月"
15 FROM (
16 SELECT TO_CHAR(LOGTIME,'YYYYMM') LOGDATE,
17 USERNAME,
18 COUNT(1) WORK_COUNTS
19 FROM TEST_DATE
20 WHERE WEEKDAY NOT IN('星期六','星期日')
21 GROUP BY TO_CHAR(LOGTIME,'YYYYMM'), USERNAME
22 )TT,
23 (SELECT TO_CHAR(MIN_DATE + RN - 1, 'YYYYMM') SUM_DATE, COUNT(1) COUNTS
24 FROM (SELECT ROWNUM RN
25 FROM DUAL
26 CONNECT BY ROWNUM <=
27 (SELECT LAST_DAY(TO_DATE(TO_CHAR(MAX(LOGTIME), 'YYYY') || '12',
28 'YYYYMM')) -
29 TRUNC(MIN(LOGTIME), 'YYYY')
30 FROM TEST_DATE)) MYDATES,
31 (SELECT TRUNC(MIN(LOGTIME), 'YYYY') MIN_DATE FROM TEST_DATE) ZZ
32 WHERE DECODE(TO_CHAR(MIN_DATE + RN - 1,
33 'd',
34 'NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'''),
35 7,
36 0,
37 1,
38 0,
39 1) = 1
40 GROUP BY TO_CHAR(MIN_DATE + RN - 1, 'YYYYMM'))YY
41 WHERE SUBSTR(SUM_DATE,0,4) = SUBSTR(LOGDATE,0,4)
42 GROUP BY SUBSTR(LOGDATE,0,4), USERNAME;年份 姓 名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月
-------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2008 SAM 0 0 0 0 0 0 0 0 0 0 17 0
2008 ROSE 0 0 0 0 0 0 0 0 0 0 18 0
引用LZ上面的说法:如果大家都没有登录,就当作放假.那么就不用假设必能有一个人是全勤了,完全可以实现你的要求了.如果有时是长假调休,导致周六或周日要上班,那你完全可以把 WHERE WEEKDAY NOT IN ('星期六', '星期日')这个条件去掉.
后边竟然还有人not in 星期六星期天
我考 还有 元旦呢 还有 国情呢 还有中秋节呢
万一公司集体出游呢?select distinct LOGTIME from tablename where 本月条件(这个条件自己写吧) 这句话就是这个月上半的时间了 select distinct USERNAME from tablename where 本月条件(这个条件自己写吧) 这句话就是这个月上班的所有人了 如果不仅仅本月 就不要where条件 就可以了
-- 最终版如下:SQL> SELECT ROWNUM AS "序号", T.*
2 FROM (SELECT USERNAME AS "姓名",
3 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '01' THEN 1 ELSE 0 END) "一月",
4 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '02' THEN 1 ELSE 0 END) "二月",
5 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '03' THEN 1 ELSE 0 END) "三月",
6 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '04' THEN 1 ELSE 0 END) "四月",
7 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '05' THEN 1 ELSE 0 END) "五月",
8 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '06' THEN 1 ELSE 0 END) "六月",
9 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '07' THEN 1 ELSE 0 END) "七月",
10 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '08' THEN 1 ELSE 0 END) "八月",
11 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '09' THEN 1 ELSE 0 END) "九月",
12 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '10' THEN 1 ELSE 0 END) "十月",
13 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '11' THEN 1 ELSE 0 END) "十一月",
14 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '12' THEN 1 ELSE 0 END) "十二月",
15 SUM(CASE WHEN WEEKDAY IS NULL THEN 1 ELSE 0 END) AS "总数"
16 FROM (SELECT C.USERNAME,C.LOGTIME, D.WEEKDAY
17 FROM (SELECT A.USERNAME, B.LOGTIME, B.WEEKDAY
18 FROM (SELECT DISTINCT USERNAME
19 FROM KQB
20 ORDER BY USERNAME) A,
21 (SELECT DISTINCT LOGTIME, WEEKDAY
22 FROM KQB) B) C
23 LEFT JOIN KQB D ON D.USERNAME = C.USERNAME AND D.LOGTIME = C.LOGTIME AND D.WEEKDAY = C.WEEKDAY)
24 GROUP BY USERNAME) T
25 / 序号 姓名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 总数
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 SAM 0 0 0 0 0 0 0 0 0 0 0 0 0
2 ROSE 0 0 0 0 0 0 0 0 0 0 3 0 3SQL>
1.关于 sleepzzzzz 说的做法,我试了好像只是查出上班的次数,不是缺勤的数次2.关于 mantisXF 说的做法,我测试一下,如果是12月的话,我有一条记录的,但我查出来后显示为21,我打开日历看看,应该正常上班时间是22天,意思就是少了一天,还有总数没算出来小弟的能力差,希望各位大哥不要说我,现在很希望大家能够帮忙解决一下
ID USERNAME LOGTIME WEEKDAY
174 lsm 26-11月-08 星期三
178 lsm 24-11月-08 星期一
179 lsm 28-11月-08 星期五
176 lsm 26-12月-08 星期五
177 lsm 27-11月-08 星期四
173 lsm 25-11月-08 星期二
175 lqz 26-11月-08 星期三 我用你的SQL执行后,是这样的结构
1 lsm 0 0 0 0 0 0 0 0 0 0 1 0 1
2 lqz 0 0 0 0 0 0 0 0 0 0 5 1 6这样不是现在出勤的次数吗,我是要缺勤的
SQL> select * from KQB; ID USERNAME LOGTIME WEEKDAY
--------------------------------------- ---------- ----------- ----------
174 lsm 2008-11-26 星期三
178 lsm 2008-11-24 星期一
179 lsm 2008-11-28 星期五
176 lsm 2008-12-26 星期五
177 lsm 2008-11-27 星期四
173 lsm 2008-11-25 星期二
175 lqz 2008-11-26 星期三7 rows selectedSQL>
SQL> SELECT ROWNUM AS "序号", T.*
2 FROM (SELECT USERNAME AS "姓名",
3 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '01' THEN 1 ELSE 0 END) "一月",
4 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '02' THEN 1 ELSE 0 END) "二月",
5 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '03' THEN 1 ELSE 0 END) "三月",
6 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '04' THEN 1 ELSE 0 END) "四月",
7 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '05' THEN 1 ELSE 0 END) "五月",
8 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '06' THEN 1 ELSE 0 END) "六月",
9 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '07' THEN 1 ELSE 0 END) "七月",
10 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '08' THEN 1 ELSE 0 END) "八月",
11 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '09' THEN 1 ELSE 0 END) "九月",
12 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '10' THEN 1 ELSE 0 END) "十月",
13 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '11' THEN 1 ELSE 0 END) "十一月",
14 SUM(CASE WHEN WEEKDAY IS NULL AND TO_CHAR(LOGTIME, 'mm')= '12' THEN 1 ELSE 0 END) "十二月",
15 SUM(CASE WHEN WEEKDAY IS NULL THEN 1 ELSE 0 END) AS "总数"
16 FROM (SELECT C.USERNAME,C.LOGTIME, D.WEEKDAY
17 FROM (SELECT A.USERNAME, B.LOGTIME, B.WEEKDAY
18 FROM (SELECT DISTINCT USERNAME
19 FROM KQB
20 ORDER BY USERNAME) A,
21 (SELECT DISTINCT LOGTIME, WEEKDAY
22 FROM KQB) B) C
23 LEFT JOIN KQB D ON D.USERNAME = C.USERNAME AND D.LOGTIME = C.LOGTIME AND D.WEEKDAY = C.WEEKDAY)
24 GROUP BY USERNAME) T
25 / 序号 姓名 一月 二月 三月 四月 五月 六月 七月 八月 九月 十月 十一月 十二月 总数
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 lsm 0 0 0 0 0 0 0 0 0 0 0 0 0
2 lqz 0 0 0 0 0 0 0 0 0 0 4 1 5SQL>
其他的sum一下就可以
考虑加班的话,那就麻烦了,呵呵,linzhangs轮到你写了.要忙手头的话去了.
改成
FROM TEST_DATE)+1) MYDATES
试试看~~
SELECT
ID,USERNAME,t_year,
MAX(CASE WHEN t_month= '01' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '一月',
MAX(CASE WHEN t_month= '02' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '二月',
MAX(CASE WHEN t_month= '03' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '三月',
MAX(CASE WHEN t_month= '04' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '四月',
MAX(CASE WHEN t_month= '05' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '五月',
MAX(CASE WHEN t_month= '06' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '六月',
MAX(CASE WHEN t_month= '07' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '七月',
MAX(CASE WHEN t_month= '08' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '八月',
MAX(CASE WHEN t_month= '09' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '九月',
MAX(CASE WHEN t_month= '10' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '十月',
MAX(CASE WHEN t_month= '11' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '十一月',
MAX(CASE WHEN t_month= '12' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays) END) '十二月'
FROM
(SELECT id,
username,
to_char(logtime,'YYYY') t_year,
to_char(logtime,'MM') t_month,
COUNT(DISTINCT trunc(logtime)) actual_days,
last_day(logtime)+1-trunc(logtime,'MM') total_days /*用当月的最后一天减当月的第一天+1得到本月天数*/,
trunc(trunc(logtime,'MM'),'Day') first_week, /*本月份的第一周的起始日期*/
to_number(to_char(logtime,'MM'))-to_number(to_char(trunc(trunc(logtime,'MM'),'Day'),'MM')) BeforeNotInDays, /*第一周中不落在本月的天数*/
trunc(last_day(logtime),'Day') last_week, /*本月份的最后一周的开始日期*/
to_number(to_char(trunc(last_day(logtime),'Day')+6,'MM'))-to_number(to_char(logtime,'MM')) AfterNotInDays, /*最后一周中不落在本月的天数*/
(trunc(last_day(logtime),'Day')-trunc(trunc(logtime,'MM'),'Day'))/7+1 weeks
FROM YourTable
GROUP BY ID,USERNAME,to_char(logtime,'YYYY'),to_char(logtime,'MM'),trunc(logtime,'MM'),last_day(logtime)
)
GROUP BY ID,USERNAME,t_year
SELECT
ID,USERNAME,t_year,
MAX(CASE WHEN t_month= '01' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '一月',
MAX(CASE WHEN t_month= '02' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '二月',
MAX(CASE WHEN t_month= '03' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '三月',
MAX(CASE WHEN t_month= '04' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '四月',
MAX(CASE WHEN t_month= '05' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '五月',
MAX(CASE WHEN t_month= '06' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '六月',
MAX(CASE WHEN t_month= '07' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '七月',
MAX(CASE WHEN t_month= '08' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '八月',
MAX(CASE WHEN t_month= '09' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '九月',
MAX(CASE WHEN t_month= '10' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '十月',
MAX(CASE WHEN t_month= '11' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '十一月',
MAX(CASE WHEN t_month= '12' THEN total_days-(weeks*2-BeforeNotInDays-AfterNotInDays)-actual_days END) '十二月'
FROM
(SELECT id,
username,
to_char(logtime,'YYYY') t_year,
to_char(logtime,'MM') t_month,
COUNT(DISTINCT trunc(logtime)) actual_days,
last_day(logtime)+1-trunc(logtime,'MM') total_days /*用当月的最后一天减当月的第一天+1得到本月天数*/,
trunc(trunc(logtime,'MM'),'Day') first_week, /*本月份的第一周的起始日期*/
to_number(to_char(logtime,'MM'))-to_number(to_char(trunc(trunc(logtime,'MM'),'Day'),'MM')) BeforeNotInDays, /*第一周中不落在本月的天数*/
trunc(last_day(logtime),'Day') last_week, /*本月份的最后一周的开始日期*/
to_number(to_char(trunc(last_day(logtime),'Day')+6,'MM'))-to_number(to_char(logtime,'MM')) AfterNotInDays, /*最后一周中不落在本月的天数*/
(trunc(last_day(logtime),'Day')-trunc(trunc(logtime,'MM'),'Day'))/7+1 weeks
FROM YourTable
GROUP BY ID,USERNAME,to_char(logtime,'YYYY'),to_char(logtime,'MM'),trunc(logtime,'MM'),last_day(logtime)
)
GROUP BY ID,USERNAME,t_year
ID USERNAME LOGTIME WEEKDAY
174 lsm 26-11月-08 星期三
178 lsm 24-11月-08 星期一
179 lsm 28-11月-08 星期五
176 lsm 26-12月-08 星期五
177 lsm 27-11月-08 星期四
173 lsm 25-11月-08 星期二
175 lqz 26-11月-08 星期三例如我今天登陆,我想查到我离前一次登陆有多少个工作日没登陆,怎么写呢