oracle中查找小于指定日期的最大日期

Question

情况如下：
有表A和B
表A中记录了周一到周五的数据，表B记录日期，包含周六周日
现在要做一个查询，结果需要包含周一到周日的数据其中周一到周五的数据直接从表A中取，周六周日直接取周五的数据。
如果碰到国庆等节假日，则取节假日前的最后一个工作日的数据。假设表A --该表数据量较大
(
d_date 日期,--只包含周一到周五
f_data 数据
) 
表B   
(
d_date 日期 --包含周一到周日
)请问SQL该如何匹配？有什么分析函数可以使用么？

Answer 1

首先怎么定义节假日需要明确建议用union all分多步可以容易实现

Answer 2

节假日，就是正常的那种节假日定义。
比如周六周日，国庆，五一等国家法定假日。union all,我想知道的是要怎么匹配，还请详细一点说明下，谢谢。

Answer 3

--oracle的不知道，sql server的可以提供一下，仅供参考。--工作日处理函数(标准节假日).sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime    --计算的结束日期
)RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE 
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO/*=================================================================*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO--在指定日期上,增加指定工作天数后的日期
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
DECLARE @bz int
--增加整周的天数
SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
,@date=DATEADD(Week,@workday/5,@date)
,@workday=@workday%5
--增加不是整周的工作天数
WHILE @workday<>0 
SELECT @date=DATEADD(Day,@bz,@date),
@workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
THEN @workday-@bz ELSE @workday END
--避免处理后的日期停留在非工作日上
WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6) 
SET @date=DATEADD(Day,@bz,@date)
RETURN(@date)
END--工作日处理函数(自定义节假日).sql
--工作日处理函数(自定义节假日)if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb_Holiday]
GO--定义节假日表
CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null)             --假日名称
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDay]
GO--计算两个日期之间的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime,  --计算的开始日期
@dt_end  datetime   --计算的结束日期
)RETURNS int
AS
BEGIN
IF @dt_begin>@dt_end
RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_begin AND @dt_end))
RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
+1-(
SELECT COUNT(*) FROM tb_Holiday
WHERE HDate BETWEEN @dt_end AND @dt_begin)))
END
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_WorkDayADD]
GO--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date    datetime,  --基础日期
@workday int       --要增加的工作日数
)RETURNS datetime
AS
BEGIN
IF @workday>0
WHILE @workday>0
SELECT @date=@date+@workday,@workday=count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
ELSE
WHILE @workday<0
SELECT @date=@date+@workday,@workday=-count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
RETURN(@date)
END

Answer 4

-------如果是国内的节假日，你必须定义一张节假日的表，来进行统计。
-------下面的只能满足你双休日的计算。你写个统计节假日的函数，加到case when的后面就ok了。select b.d_date,a.f_data 
from A,B 
where a.date=
      (case when to_char(b.d_date) in ('1','7') 
                 then trunc(b._date,'day')-2 
                 else b.d_date 
       end)

Answer 5

只能给你点思路，没数据：
首先生产一个临时表，包含周一至周日的数据，可以用你AB表产生，如：
WITH tmp AS(SELECT d_date FROM A UNION SELECT d_date FROM B) --这里用union 会自动按日期排序，为后面做准备
现在你临时表tmp就包含了所有数据
第二步，周六周日直接取周五的数据，
可以用分析函数，由于你前面临时表已经排序了，可以用lead()或者lag()分析函数来求周六和周日取周五数据
最后，你如果碰到国庆等节假日
在查询时候用case WHEN 作下日期判断就可以了

Answer 6

select a.* from a,b
where a.d_date=decode(to_char(b.d_date,'day'),'星期六','星期五','星期日','星期五',to_char(b.d_date,'day'))
union 
select a.* from a,
(select case when to_char(d_date,'mmdd')>=1001 and to_char(d_date,'mmdd')<=1007
then (case when to_char(d_date,'mmdd')=1001 then d_date-1 end) else d_date end d_date
from b)
where a.d_date=decode(to_char(b.d_date,'day'),'星期六','星期五','星期日','星期五',to_char(b.d_date,'day'))
....

Answer 7

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott
SQL> select * from A;D_DATE          F_DATA
----------- ----------
2010-11-8            8
2010-11-9            9
2010-11-10          10
2010-11-11          11
2010-11-12          12
2010-11-15          15
2010-11-16          16
2010-11-17          17
2010-11-18          18
2010-11-19          19
2010-10-1           19
2010-9-30           19
2010-9-29           19
2010-9-28           19
2010-9-27           19
2010-9-19           19
2010-9-20           19
2010-9-24           19
2010-9-23           19
2010-9-21           1920 rows selectedSQL> select * from B;D_DATE
-----------
2010-11-7
2010-11-6
2010-11-8
2010-11-9
2010-11-10
2010-11-11
2010-11-12
2010-11-13
2010-11-14
2010-11-15
2010-11-16
2010-11-17
2010-11-18
2010-11-19
2010-10-1
2010-5-1
2010-5-217 rows selected
----建立节假日的映射表
SQL> select * from scott.holiday;HOLIDAY     REMARK
----------- --------------------------------------------------------------------------------
2010-10-1   国庆节
2010-5-1    五一劳动节
-------创建是否为节假日的函数。
SQL> 
SQL> create or replace function isHoliday(enterDate in date) return number as
  2    flag       number;
  3    returnDate date;
  4  begin
  5    select holiday into returnDate from scott.holiday where holiday = enterDate;
  6    return 1;
  7  exception
  8    when others then
  9      return 0;
 10  end;
 11  /Function created
----如果是节假日，取前一个工作日，即是星期五，如果是双休日，取星期五，否则取当天
SQL> 
SQL> select a.f_data, a.d_date
  2    from A, B
  3   where a.d_date =
  4         (case when scott.isholiday(b.d_date) = 1 then
  5          trunc(b.d_date, 'day') - 2 when to_char(b.d_date, 'd') in ('1', '7') then
  6          trunc(b.d_date, 'day') - 2 else b.d_date end)
  7  /    F_DATA D_DATE
---------- -----------
         8 2010-11-8
         9 2010-11-9
        10 2010-11-10
        11 2010-11-11
        12 2010-11-12
        12 2010-11-12
        15 2010-11-15
        16 2010-11-16
        17 2010-11-17
        18 2010-11-18
        19 2010-11-19
        19 2010-9-2412 rows selectedSQL>