用Oracle日期函数解决查询难题

最近，笔者参与开发了一个信息管理系统，系统以ORACLE为数据库后台,PowerBuilder8为开发工具。

笔者负责开发的查询模块中需要一个全局函数，该函数能查询某年、某月、某职工在某个城市的工作总天数，查询仅涉及一个表ryqxb，该表记录了人员去向，表结构如下：pepoleid：职工编码，city：所工作的城市，qssj：工作起始时间，zzsj：工作终止时间。查询方案似乎很简单：

首先，每次工作总天数=工作终止日期-工作起始日期+1。

然后，利用SUM函数，对符合查询条件的每次按次工作总天数求和，得出当月工作天数。

仔细分析，笔者发现这个查询非常复杂，查询涉及的SQL语句需要同时考虑以下二种情况：

1、某职工在某城市工作起始日期和终止日期属于同一年同一月，且在要查询的年月范围内，计算可直接采用前面的查询方案。比如：某职工从2006年7月5日至2006年7月15日，在某城市工作，则7月份的总工作天数为：7月15日减7月5日加1，总共11天。

2、某职工在某城市工作起始日期和终止日期不属于同一月，且要查询的年月在工作起止日期的范围内，计算总工作天数不能完全以表中记录的起止日期为计算依据。比如：某职工2005年12月25日至2006年2月20日在某城市工作。 2005年12月的总工作天数为:2005年12月31日减2005年12月25日加1，总共7天。2006年1月的总工作天数为:2006年1月31日减2006年1月1日加1总共31天。2006年2月的总工作天数为:2006年2月20日减2006年2月1日加1总共21天。这种情况如何确定计算依据呢？根据这个例子，做出示意图：

仔细分析该图，不难发现规律：计算总天数所依据的起始日期为表中记录的起始日期和查询年月第一天之间的最大者，计算总天数所依据的终止日期为表中记录的终止日期和查询年月最后一天之间的最小者。

该规律对第一种情况同样有效，但要利用该规律，必须有数据库管理系统的支持，Oracle提供了相关的SQL函数：

1、last_day（d）：返回包含了日期参数的月份的最后一天的日期

2、greatest（d1，d2…dn）给出的日期列表中最后的日期

3、least（d1，k2…dn）给出的日期列表中最早的日期

利用这三个日期函数，笔者仅用一条较简单的SQL语句就完成了函数，以下是源代码：

//函数名：gf_getmonthdays

//函数功能：返回某年某月某个职工在某城市的工作总天数

//函数参数：as_year年,as_month月,as_id职工ID,as_city城市名

long gf_getmonthdays(string as_year,stringas_month,string as_id, string as_city)

string ls_firstdate

String ls_yearmonth

long  ll_days

ls_firstdate=as_year+'-'+trim(as_month)+'-01'

ls_yearmonth=as_year+'-'+trim(as_month)

selectsum(least(t.zzsj,last_day(to_date(:ls_firstdate,'yyyy-mm-dd'))) -greatest(to_date(:ls_firstdate,'yyyy-mm-dd'),t.qssj)+1) asdays

  into :ll_days fromryqxb t

  WHERE t.pepoleid=:as_id and  t.city=:as_city

      and(to_char(t.zzsj,'yyyy-mm')>=:ls_yearmonth

            andto_char(t.qssj,'yyyy-mm')<=:ls_yearmonth);