如何处理节假日问题

[ladofwind]

我的表如下

table money

quantity number(10),

cdate number(10)


实际上就是存入一个数量和一个时间,理论上一个工作日有一条数据,

前台要提供查询某一天数量,因为节假日及周末没有记录,如何查出这个数量呢,

从概念上应该说是如果当天没有,就往前查看最近一条有数量的数.

sql如何写最简洁.

[fxianxian]

select *
  from (select quantity
          from money
         where sdate <= variable_date
         order by sdate desc)
 where rownum = 1

仅供参考

[liusong_china]

从概念上应该说是如果当天没有,就往前查看最近一条有数量的数.

SELECT quantity FROM (SELECT quantity FROM tb WHERE cdate <= '20091214' ORDER BY cdate DESC) WHERE rownum = 1;

[luyi6101]

楼上的2位都有bug，比如前一天也没有这么办，所以要考虑连续节假日的问题

[luyi6101]

SELECT *
	FROM (SELECT qty
					FROM money
				 WHERE cdate <= DATE '2009-10-2'
					 AND qty != 0
				 ORDER BY cdate DESC)
 WHERE rownum = 1

[Phoenix_99]

select * from (
select quantity,cdate from money where cdate <to_date('2009-12-13','yyyy-mm-dd') order by cdate desc
) where rownum = 1;

[ACMAIN_CHM]

需要再建一张表 wkcalendar ( wkDate date primary key, wkType) wkType : 0 工作日，7周末，9法定国假。

然后直接 select cont(*) 就行了