本帖最后由 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);
但我想写成第一段那种比较标准的格式,但弄了好长时间不行,各种试啊,还是弄不出来,请大侠帮帮忙!!!!