oracle 没数据显示0,有一天没有交易记录，怎么也能在数据库内显示为0？

本帖最后由 hnzzgmy 于 2013-7-31 16:56 编辑

各位大侠：

SELECT NVL(T.DAY_ID,TO_NUMBER(TO_CHAR(V_SDATE,'YYYYMMDD'))) AS DAYID,

T3.A,T3.B,T3.C,NVL(COUNT(DISTINCT T.ID),0),TRUNC(SYSDATE) AS LOADDATE

FROM TABLE1 T

INNER JOIN TABLE2 T1 ON T.ID=T1.ID

INNER JOIN TABLE2 T2 ON T1.SUBID=T2.SUBID

RIGHT JOIN TABLE4 T3 ON T.OTHERID=T3.OTHERID

WHERE T3.MAINID=1001

AND T.DAY_ID BETWEEN 20130701 AND 20130730

GROUP BY NVL(T.DAY_ID,TO_NUMBER(TO_CHAR(V_SDATE,'YYYYMMDD'))),

T3.A,T3.B,T3.C,TRUNC(SYSDATE);

以上SQL是用作关联往表内插入数据的.

我有四张表，要关联出业务数据，现在遇到这样的问题：

在关联出来的20130710是有交易记录数据的，但20130711没有交易记录数据的，20130712有交易记录数据，

我想把20130711当日的数据全弄成0，怎么弄？

我试了，可以用弄成表跟表用逗号隔开的方式如下：

SELECT NVL(T.DAY_ID,TO_NUMBER(TO_CHAR(V_SDATE,'YYYYMMDD'))) AS DAYID,

T3.A,T3.B,T3.C,NVL(COUNT(DISTINCT T.ID),0),TRUNC(SYSDATE) AS LOADDATE

FROM (selct t.*

FROM TABLE1 T

INNER JOIN TABLE2 T1 ON T.ID=T1.ID

INNER JOIN TABLE2 T2 ON T1.SUBID=T2.SUBID) t,

TABLE4 T3

WHERE T3.MAINID=1001 AND T.OTHERID=T3.OTHERID

AND T.DAY_ID(+) BETWEEN 20130701 AND 20130730

GROUP BY NVL(T.DAY_ID,TO_NUMBER(TO_CHAR(V_SDATE,'YYYYMMDD'))),

T3.A,T3.B,T3.C,TRUNC(SYSDATE);

但我想写成第一段那种比较标准的格式，但弄了好长时间不行，各种试啊，还是弄不出来，请大侠帮帮忙！！！！