我用以下语句来显示一个报表,total_amount字段是从另一个表中查询得来的,但是如果total_amount没有END_DATE这一个的数据时,它会自动用前一天的数据,若没有则再往前一天。[code]
select "PRODUCT"."ID" as "ID",
"PRODUCT"."BRAND" as "BRAND",
( select amount from TOTAL where date = :END_DATE and id = "PRODUCT"."ID" ) as "TOTAL_AMOUNT"
from "PRODUCT"
[/code]我现在设想的办法是使用max(date)的办法:
if 存在end_date这一天的记录? then
select amount from TOTAL where date = :END_DATE and id = "PRODUCT"."ID";
else
显示与end_date之前最近一天的数据;
end if具体要怎么样实现却没有头绪,请诸位牛人帮帮忙,十分感谢!
select "PRODUCT"."ID" as "ID",
"PRODUCT"."BRAND" as "BRAND",
( select amount from TOTAL where date = :END_DATE and id = "PRODUCT"."ID" ) as "TOTAL_AMOUNT"
from "PRODUCT"
[/code]我现在设想的办法是使用max(date)的办法:
if 存在end_date这一天的记录? then
select amount from TOTAL where date = :END_DATE and id = "PRODUCT"."ID";
else
显示与end_date之前最近一天的数据;
end if具体要怎么样实现却没有头绪,请诸位牛人帮帮忙,十分感谢!
name,
(select amount from TOTAL where date = :END_DATE and id = "PRODUCT"."ID") as amount
from product
SELECT PRODUCT.*,
(SELECT AMOUNT
FROM TOTAL
WHERE DATECOL IN (SELECT MAX(DATECOL)
FROM TOTAL
WHERE ID = PRODUCT.ID
AND AMOUNT IS NOT NULL
AND DATECOL <= PRODUCT.END_DATE)
AND ROWNUM = 1) AMOUNT
FROM PRODUCT;
SQL> SELECT *
2 FROM TOTAL; PID AMOUNT END_DATE
---------- ---------- -----------
1 44 7/6/2008
1 33 7/1/2008
1 67 7/8/2008
1 21 7/3/2008
2 78 7/5/2008
2 15 7/7/2008
2 23 7/3/20087 rows selectedSQL> SQL> SELECT DISTINCT FIRST_VALUE(AMOUNT) OVER(ORDER BY END_DATE DESC) AS "NEW_AMOUNT"
2 FROM TOTAL,
3 (
4 SELECT TO_DATE('2008-07-02','YYYY-MM-DD') ENTER_DATE
5 FROM DUAL
6 )
7 WHERE TRUNC(ENTER_DATE) - TRUNC(END_DATE) >= 0
8 AND PID = 1
9 ;NEW_AMOUNT
----------
33SQL>
我现在按3楼所说的进行,试验的结果符合我的要求,但是有一点不明白,第3个select子句它返回的应该是一个最大日期,当它作为第2个select的条件时,查询出来的应该只是最大日期的amount,它这里是不是也要像第3个select一样,要加上id = product.id呢?
4楼的我看不明白,所以就没有按这个来试,水平有限, -_-!!