请教一个关于oracle处理考勤记录的sql语句

有一个考勤记录表
[code="java"]
create table t_kaoqin
(
EMPID VARCHAR2(255 CHAR),//员工ID
type VARCHAR2(255 CHAR),//旷工、事假、病假、婚假
EMPCODE VARCHAR2(255 CHAR),//员工编号
DAYS NUMBER,//天数
TIME DATE,//旷工(事假、病假、婚假)时间,
EMPNAME VARCHAR2(255 CHAR),//姓名
POST VARCHAR2(255 CHAR),//职位
DEPT VARCHAR2(255 CHAR),//部门

)
[/code]
需要实现一个查询效果如下的sql，请问数据库高手们，这个sql应该如何取实现
编号 姓名 部门 职位 月份 旷工天数 事假天数 病假天数 婚假天数
001 张三 财务部 经理 2011-02 1 0 0 0
001 张三 财务部 经理 2011-05 0 2 0 0
002 李四 人事部 经理 2011-03 0 0 1 0

create table YEAR
(
ALL_YEAR VARCHAR2(6),
YEAR VARCHAR2(4),
MONTH VARCHAR2(8),
MONTH1 VARCHAR2(8)
)
;
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201101', '2011', '一月', '01');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201102', '2011', '二月', '02');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201103', '2011', '三月', '03');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201104', '2011', '四月', '04');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201105', '2011', '五月', '05');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201106', '2011', '六月', '06');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201107', '2011', '七月', '07');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201108', '2011', '八月', '08');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201109', '2011', '九月', '09');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201110', '2011', '十月', '10');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201111', '2011', '十一月', '11');
insert into YEAR (ALL_YEAR, YEAR, MONTH, MONTH1)
values ('201112', '2011', '十二月', '12');
commit;

--给个统计所有员工考情记录的sql
SELECT 'E' || T1.EMPCODE 编号,T1.EMPNAME 姓名,T1.DEPT 部门,T1.POST 职位,
DECODE(T.月份,B.MONTH1,T.旷工,0) 旷工,DECODE(T.月份,B.MONTH1,T.事假,0) 事假,
DECODE(T.月份,B.MONTH1,T.病假,0) 病假,DECODE(T.月份,B.MONTH1,T.婚假,0) 婚假,
B.YEAR 年份,B.MONTH 月份

FROM (SELECT TO_CHAR(A.TIME, 'YYYYMM') ALL_YEAR,
A.EMPCODE 编号,A.EMPNAME 姓名,A.DEPT 部门,A.POST 职位,
TO_CHAR(A.TIME, 'YYYY') 年份,TO_CHAR(A.TIME, 'MM') 月份,
SUM(DECODE(A.TYPE,'旷工',A.DAYS,0)) 旷工,SUM(DECODE(A.TYPE,'事假',A.DAYS,0)) 事假,
SUM(DECODE(A.TYPE,'病假',A.DAYS,0)) 病假,SUM(DECODE(A.TYPE,'婚假',A.DAYS,0)) 婚假
FROM (SELECT DISTINCT(C.EMPCODE),C.EMPNAME,C.POST,C.DEPT,C.TYPE,C.TIME,C.DAYS FROM T_KAOQIN C) A

GROUP BY A.EMPCODE,A.EMPNAME,A.DEPT,A.POST,TO_CHAR(A.TIME, 'YYYY'),TO_CHAR(A.TIME, 'MM'),TO_CHAR(A.TIME, 'YYYYMM')
ORDER BY A.EMPCODE,TO_CHAR(A.TIME, 'MM')
) T,YEAR B,(SELECT DISTINCT(C.EMPCODE),C.EMPNAME,C.POST,C.DEPT FROM T_KAOQIN C) T1
WHERE B.ALL_YEAR =T.ALL_YEAR(+)
ORDER BY T1.EMPCODE,B.MONTH1

--结果

001 张三 财务部 经理0000 2011 01
001 张三 财务部 经理1000 2011 02
001 张三 财务部 经理0010 2011 03
001 张三 财务部 经理0000 2011 04
001 张三 财务部 经理0000 2011 05
001 张三 财务部 经理0000 2011 06
001 张三 财务部 经理0000 2011 07
001 张三 财务部 经理0000 2011 08
001 张三 财务部 经理0000 2011 09
001 张三 财务部 经理0000 2011 10
001 张三 财务部 经理0000 2011 11
001 张三 财务部 经理0000 2011 12
002 李四 人事部经理0000 2011 01
002 李四 人事部经理1000 2011 02
002 李四 人事部经理0010 2011 03
002 李四 人事部经理0000 2011 04
002 李四 人事部经理0000 2011 05
002 李四 人事部经理0000 2011 06
002 李四 人事部经理0000 2011 07
002 李四 人事部经理0000 2011 08
002 李四 人事部经理0000 2011 09
002 李四 人事部经理0000 2011 10
002 李四 人事部经理0000 2011 11
002 李四 人事部经理0000 2011 12

-oracle 字段t1：商家cd t2：托盘cd t3：空数量

--测试过，能用，不知是否满足要求？

select t.t1,

sum (decode(t.t2, 'm1' ,t.t3,0))m1,

sum (decode(t.t2, 'm2' ,t.t3,0))m2,

sum (decode(t.t2, 'm3' ,t.t3,0))m3,

sum (decode(t.t2, 'm4' ,t.t3,0))m4,

sum (decode(t.t2, 'm8' ,t.t3,0))m8

from a t

group by t.t1

行转列

用oracle字符截取 截

SUBSTRING
返回字符、binary、text 或 image 表达式的一部分。有关可与该函数一起使用的有效 Microsoft® SQL Server™ 数据类型的更多信息，请参见数据类型。

语法
SUBSTRING ( expression , start , length )

参数
expression

是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。

start

是一个整数，指定子串的开始位置。

length

是一个整数，指定子串的长度（要返回的字符数或字节数）。

substring()
——任意位置取子串

left()
right()
——左右两端取子串

ltrim()
rtrim()
——截断空格，没有trim()。

charindex()
patindex()
——查子串在母串中的位置，没有返回0。区别：patindex支持通配符，charindex不支持。

函数功效：
字符串截取函数，只限单字节字符使用（对于中文的截取时遇上奇数长度是会出现乱码，需另行处理），本函数可截取字符串指定范围内的字符。

应用范围：
标题、内容截取

函数格式：
string substr ( string string, int start [, int length])
参数1：处理字符串
参数2：截取的起始位置（第一个字符是从0开始）
参数3：截取的字符数量
substr()更多介绍可在PHP官方手册中查询（字符串处理函数库）

举例：
substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符
substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符
substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符
substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
substr("ABCDEFG", 0, -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变

例子:

1.截取已知长度的函数

A.截取从字符串左边开始N个字符

Declare @S1 varchar(100) 
Select @S1='http://www.163.com' 
Select Left(@S1,4) 
------------------------------------ 
显示结果: http 

B.截取从字符串右边开始N个字符(例如取字符www.163.com)

Declare @S1 varchar(100) 
Select @S1='http://www.163.com' 
Select right(@S1,11)  
------------------------------------ 
显示结果: www.163.com 

C.截取字符串中任意位置及长度(例如取字符www)

Declare @S1 varchar(100) 
Select @S1='http://www.163.com' 
Select SUBSTRING(@S1,8,3)  
------------------------------------ 
显示结果: www.163.com 

以上例子皆是已知截取位置及长度,下面介绍未知位置的例子 

2.截取未知位置的函数

A.截取指定字符串后的字符串(例如截取http://后面的字符串)

方法一: 

Declare @S1 varchar(100) 
Select @S1='http://www.163.com'  
Select Substring(@S1,CHARINDEX('www',@S1)+1,Len(@S1)) 
/*此处也可以这样写:Select Substring(@S1,CHARINDEX('//',@S1)+2,Len(@S1))*/ 
------------------------------------ 
显示结果: www.163.com 


需要注意:CHARINDEX函数搜索字符串时,不区分大小写,因此CHARINDEX('www',@S1)也可以写成CHARINDEX('WWW',@S1) 

方法二:(与方法一类似) 

Declare @S1 varchar(100) 
Select @S1='http://www.163.com'  
Select Substring(@S1,PATINDEX('%www%',@S1)+1,Len(@S1)) 
--此处也可以这样写:Select Substring(@S1,PATINDEX('%//%',@S1)+2,Len(@S1)) 
------------------------------------ 
显示结果: www.163.com  

函数PATINDEX与CHARINDEX区别在于:前者可以参数一些参数,增加查询的功能

方法三: 

Declare @S1 varchar(100) 
Select @S1='http://www.163.com'  
Select REPLACE(@S1,'http://','') 
------------------------------------ 
显示结果: www.163.com 

利用字符替换函数REPLACE,将除需要显示字符串外的字符替换为空

方法四: 

Declare @S1 varchar(100) 
Select @S1='http://www.163.com'  
Select STUFF(@S1,CHARINDEX('http://',@S1),Len('http://'),'') 
------------------------------------ 
显示结果: www.163.com  

函数STUFF与REPLACE区别在于:前者可以指定替换范围,而后者则是全部范围内替换

B.截取指定字符后的字符串(例如截取C:/Windows/test.txt中文件名)
与A不同的是,当搜索对象不是一个时,利用上面的方法只能搜索到第一个位置

方法一: 

Declare @S1 varchar(100) 
Select @S1='C:/Windows/test.txt' 
select right(@S1,charindex('/',REVERSE(@S1))-1) 
------------------------------------- 
显示结果: text.txt 

利用函数REVERSE获取需要截取的字符串长度

substr()

例子:

private void DDL_AreaBind()
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strcon"].ConnectionString);
string str = "0000";
cmd = new SqlCommand("select AreaID,Name=ltrim(Name) from Area where right(AreaID,4) ='" + str + "'", conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds, "area");
this.ddl_area.DataSource = ds.Tables["area"].DefaultView;
this.ddl_area.DataTextField = "Name";
this.ddl_area.DataValueField = "AreaID";
this.ddl_area.DataBind();

          cmd = new SqlCommand("select * from Area    ", conn); 
          cmd.CommandType = CommandType.Text; 
          SqlDataAdapter adapter = new SqlDataAdapter(cmd); 
          adapter.Fill(ds, "city"); 
          this.ddl_city.DataSource = ds.Tables["city"].DefaultView; 
          this.ddl_city.DataTextField = "Name"; 
          this.ddl_city.DataValueField = "AreaID"; 
          this.ddl_city.DataBind(); 
      } 

protected void ddl_area_SelectedIndexChanged(object sender, EventArgs e)
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["strcon"].ConnectionString);
this.ddl_city.Enabled = true;
string str1="0000";
cmd = new SqlCommand("select AreaID,Name from Area where substring(AreaID,1,2)='" + this.ddl_area.SelectedValue.Substring(0,2) + "' AND substring(AreaID,3,4) <> '0000' AND substring(AreaID,5,2)='00' ", conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "city");
this.ddl_city.DataSource = ds.Tables["city"].DefaultView;
this.ddl_city.DataTextField = "Name";
this.ddl_city.DataValueField = "AreaID";
this.ddl_city.DataBind();
}

trunc(t.time,'MM')

SELECT A.EMPCODE 编号,A.EMPNAME 姓名,A.DEPT 部门,A.POST 职位, TO_CHAR(A.TIME, 'YYYYMM') 月份,
SUM(DECODE(A.TYPE,'旷工',A.DAYS,0)) 旷工,
SUM(DECODE(A.TYPE,'事假',A.DAYS,0)) 事假,
SUM(DECODE(A.TYPE,'病假',A.DAYS,0)) 病假,
SUM(DECODE(A.TYPE,'婚假',A.DAYS,0)) 婚假
FROM T_KAOQIN A
GROUP BY A.EMPCODE,EMPNAME,A.DEPT,POST,TO_CHAR(A.TIME, 'YYYYMM')
ORDER BY A.EMPCODE

在oralce数据库中插入数据

1 旷工 1 2 2011-7-1 张三 经理 财务部
2 事假 1 3 2011-7-6 张三 经理 财务部
3 旷工 2 3 2011-6-1 李四 经理 人事部

4 事假 1 2 2011-5-1 张三 经理 财务部
sql执行结果：
1 张三 财务部 经理 201105 0 2 0 0
1 张三 财务部 经理 201107 2 3 0 0
2 李四 人事部 经理 201106 3 0 0 0

不知道是否满足需求的。

SELECT A.EMPCODE 编号,A.EMPNAME 姓名,A.DEPT 部门,A.POST 职位, TO_CHAR(A.TIME, 'YYYYMM') 月份,
SUM(DECODE(A.TYPE,'旷工',A.DAYS,0)) 旷工,
SUM(DECODE(A.TYPE,'事假',A.DAYS,0)) 事假,
SUM(DECODE(A.TYPE,'病假',A.DAYS,0)) 病假,
SUM(DECODE(A.TYPE,'婚假',A.DAYS,0)) 婚假
FROM T_KAOQIN A
GROUP BY A.EMPCODE,EMPNAME,A.DEPT,POST,TO_CHAR(A.TIME, 'YYYYMM')
ORDER BY TO_CHAR(A.TIME, 'YYYYMM')

你的问题可以参考http://www.iteye.com/problems/70434，我在上面做了很详细的解释，还有问题可以发邮件给我奥！

上面解决了如果月份中没有数据显示为0的问题，因为year表中只有2011年一年的，如果需要查询其它年份的记录就要写year数据了

要做报表何不先建视图?
把你需要的字段先处理出来.
另外, oracle10g 支持正则表达式 和 高效列传行, 对查询和输出复杂格式的文本很有帮助

根据用户和请假类型分组 然后程序内存处理算了 没有必要什么都要sql解决

分组应该还要把月份放进去是把

主要考察对decode 和 substr两个函数的使用

这个问题是请假时间和 天数对月份处理的问题
用三个select 语句 union all 起来 就行
分月末的时间 - 请假时间 >=天数(day)和 月末时间 - 请假时间< 天数(day)
两种情况
[code="java"]
select empcode,
empname,
dept,
post,
monthid,
sum(decode(type, 1, days, 0)), --旷工
sum(decode(type, 2, days, 0)), --事假
sum(decode(type, 3, days, 0)), --病假
sum(decode(type, 4, days, 0)) --婚假
from (select empid,
type,
empcode,
case
when to_char(last_day(time), 'DD') - to_char(time, 'DD') + 1 <= days then
to_char(last_day(time), 'DD') - to_char(time, 'DD') + 1
end days, --天数
to_char(time, 'YYYY-MM') monthid, --月份
empname,
post,
dept
from t_kaoqin

    union all

    select empid,
           type,
           empcode,
           case
             when to_char(last_day(time), 'DD') - to_char(time, 'DD') <= days then
              days -
              (to_char(last_day(time), 'DD') - to_char(time, 'DD'))
           end days, --天数
           to_char(add_months(time + 1), 'YYYY-MM') monthid, --月份
           empname,
           post,
           dept
      from t_kaoqin

    union all
    select empid,
           type,
           empcode,
           case
             when to_char(last_day(time), 'DD') - to_char(time, 'DD') > days then
              to_char(last_day(time), 'DD') - to_char(time, 'DD') + 1
           end days, --天数
           to_char(time, 'YYYY-MM') monthid, --月份
           empname,
           post,
           dept
      from t_kaoqin)

group by empcode, empname, dept, post, monthid

[/code]

实现以下：
select EMPCODE, EMPNAME, DEPT, post, to_char(time,'yyyy-mm'), sum(decode(type,'旷工',days,0)) "旷工天数",
sum(decode(type,'事假',days,0)) "事假天数",sum(decode(type,'病假',days,0)) "病假天数",sum(decode(type,'婚假',days,0)) "婚假天数" from t_kaoqin group by EMPCODE, EMPNAME, DEPT, post,to_char(time,'yyyy-mm')

典型的一个行列互换的问题
[code="java"]

select rownum 编号,
tk.empname 姓名,
tk.dept 部门,
tk.post 职位,
to_char(tk.time, 'yyyy-MM') 月份,
sum(case tk.type when '旷工' than tk.type) 旷工,
sum(case tk.type when '事假天数' than tk.type) 事假天数,
sum(case tk.type when '病假天数' than tk.type) 病假天数,
sum(case tk.type when '婚假天数' than tk.type) 婚假天数
from t_kaoqin tk
group by tk.empname,tk.dept,tk.post,tk.time

[/code]

上面写错了一个 是then 不是 than

额 上面的我测了下 写错了 大意了

[code="java"]
select rownum 编号,
tk.empname 姓名,
tk.dept 部门,
tk.post 职位,
to_char(tk.time, 'yyyy-MM') 月份,
sum(case tk.type when '旷工' then tk.type end) 旷工,
sum(case tk.type when '事假天数' then tk.type end) 事假天数,
sum(case tk.type when '病假天数' then tk.type end) 病假天数,
sum(case tk.type when '婚假天数' then tk.type end) 婚假天数
from t_kaoqin tk
group by rownum,tk.empname,tk.dept,tk.post,tk.time

[/code]

这个正解

这个是列转行的情况，sql如下：
[code="sql"]
select distinct a.empcode,a.empname,a.dept,a.time,
(select nvl(sum(b.days),0) from t_kaoqin b where a.empcode = b.empcode and a.time = b.time and b.type = '旷工') 旷工天数,
(select nvl(sum(b.days),0) from t_kaoqin b where a.empcode = b.empcode and a.time = b.time and b.type = '事假') 事假天数,
(select nvl(sum(b.days),0) from t_kaoqin b where a.empcode = b.empcode and a.time = b.time and b.type = '病假') 病假天数,
(select nvl(sum(b.days),0) from t_kaoqin b where a.empcode = b.empcode and a.time = b.time and b.type = '婚假') 婚假天数
from t_kaoqin a
order by a.empname,a.time;
[/code]
选择结果：
[table]
EMPCODE EMPNAME DEPT TIME 旷工天数 事假天数 病假天数 婚假天数
1003 李四 质量管理部 2011-07 0 1 3 0
1002 王五 技术中心 2011-07 1 2 0 7
1001 张三 技术中心 2011-07 2 1 1 0
1001 张三 技术中心 2011-08 0 1 2 0
[/table]

不错，学习了

做个临时表，就一个字段，里面存的是 1-12。

select m.tmonth as 月份,
sum(decode(t.type,'病假',t.days,0)) as 病假,
sum(decode(t.type,'事假',t.days,0)) as 事假,
sum(decode(t.type,'婚假',t.days,0)) as 婚假,
sum(decode(t.type,'旷工',t.days,0)) as 旷工
from test_shigu_month m left outer join test_kaoqin_recode t
on nvl(to_char(to_date(t.time,'yyyy-mm-dd'),'mm'),0) = m.tmonth and t.empid='1'
group by m.tmonth
order by m.tmonth

这样写 就能把没有值的弄成0 了。