写过最长的一次sql

SELECT (NVL(SJTOTAL, 0) - NVL(TOULIAN, 0) - NVL(WANNENG, 0)) * 0.1 +
       (NVL(TOULIAN, 0)) + (NVL(WANNENG, 0) * 0.8) AS KTZ
  FROM (SELECT (SELECT (CASE
                         WHEN SEASON = 0 THEN
                          CASE
                         WHEN EXISTS
                          (SELECT *
                                 FROM USER_TABLES
                                WHERE TABLE_NAME = 'TB_002_2008_IMP_NAV') THEN
                          (SELECT SUM(NVL(FFUNDNAV, 0))
                             FROM TB_002_2008_IMP_NAV
                            WHERE FDATE IN
                                  (SELECT MAX(TT.FDATE) FDATE
                                     FROM (SELECT TO_DATE('2008-12-21',
                                                          'YYYY-MM-DD') + ROWNUM - 1 FDATE
                                             FROM USER_OBJECTS
                                            WHERE ROWNUM <= 11) TT
                                    WHERE TT.FDATE NOT IN
                                          (SELECT FDATE
                                             FROM TB_BASE_CHILDHOLIDAY
                                            WHERE FHOLIDAYSCODE = 'CH'
                                              AND FCHECKSTATE = 1
                                              AND FDATE BETWEEN
                                                  TO_DATE('2008-12-21',
                                                          'YYYY-MM-DD') AND
                                                  TO_DATE('2008-12-31',
                                                          'YYYY-MM-DD')))
                              AND FSETID IN ('3','4','5'))
                         ELSE
                          0
                       END ELSE(CASE
                                  WHEN SEASON = 1 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM TB_002_2009_IMP_NAV
                                     WHERE FDATE IN
                                           (SELECT MAX(TT.FDATE) FDATE
                                              FROM (SELECT TO_DATE('2009-03-21',
                                                                   'YYYY-MM-DD') + ROWNUM - 1 FDATE
                                                      FROM USER_OBJECTS
                                                     WHERE ROWNUM <= 11) TT
                                             WHERE TT.FDATE NOT IN
                                                   (SELECT FDATE
                                                      FROM TB_BASE_CHILDHOLIDAY
                                                     WHERE FHOLIDAYSCODE = 'CH'
                                                       AND FCHECKSTATE = 1
                                                       AND FDATE BETWEEN
                                                           TO_DATE('2009-03-21',
                                                                   'YYYY-MM-DD') AND
                                                           TO_DATE('2009-03-31',
                                                                   'YYYY-MM-DD')))
                                       AND FSETID IN ('3','4','5'))
                                  WHEN SEASON = 2 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM TB_002_2009_IMP_NAV
                                     WHERE FDATE IN
                                           (SELECT MAX(TT.FDATE) FDATE
                                              FROM (SELECT TO_DATE('2009-06-20',
                                                                   'YYYY-MM-DD') + ROWNUM - 1 FDATE
                                                      FROM USER_OBJECTS
                                                     WHERE ROWNUM <= 11) TT
                                             WHERE TT.FDATE NOT IN
                                                   (SELECT FDATE
                                                      FROM TB_BASE_CHILDHOLIDAY
                                                     WHERE FHOLIDAYSCODE = 'CH'
                                                       AND FCHECKSTATE = 1
                                                       AND FDATE BETWEEN
                                                           TO_DATE('2009-06-20',
                                                                   'YYYY-MM-DD') AND
                                                           TO_DATE('2009-06-30',
                                                                   'YYYY-MM-DD')))
                                       AND FSETID IN ('3','4','5'))
                                  ELSE
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM TB_002_2009_IMP_NAV
                                     WHERE FDATE IN
                                           (SELECT MAX(TT.FDATE) FDATE
                                              FROM (SELECT TO_DATE('2009-09-20',
                                                                   'YYYY-MM-DD') + ROWNUM - 1 FDATE
                                                      FROM USER_OBJECTS
                                                     WHERE ROWNUM <= 11) TT
                                             WHERE TT.FDATE NOT IN
                                                   (SELECT FDATE
                                                      FROM TB_BASE_CHILDHOLIDAY
                                                     WHERE FHOLIDAYSCODE = 'CH'
                                                       AND FCHECKSTATE = 1
                                                       AND FDATE BETWEEN
                                                           TO_DATE('2009-09-20',
                                                                   'YYYY-MM-DD') AND
                                                           TO_DATE('2009-09-30',
                                                                   'YYYY-MM-DD')))
                                       AND FSETID IN ('3','4','5'))
                                END) END) SJTOTAL
                  FROM (SELECT ((CASE
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 0 THEN
                                  1
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 0 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 3 THEN
                                  2
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 3 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 6 THEN
                                  3
                                 ELSE
                                  4
                               END) - 1) AS SEASON
                          FROM DUAL)) AS SJTOTAL,
               (SELECT (CASE
                         WHEN SEASON = 0 THEN
                          CASE
                         WHEN EXISTS
                          (SELECT *
                                 FROM USER_TABLES
                                WHERE TABLE_NAME = 'TB_002_2008_IMP_NAV') THEN
                          (SELECT SUM(NVL(FFUNDNAV, 0))
                             FROM (SELECT FSETID, FFUNDNAV
                                     FROM TB_002_2008_IMP_NAV
                                    WHERE FDATE IN
                                          (SELECT MAX(TT.FDATE) FDATE
                                             FROM (SELECT TO_DATE('2008-12-21',
                                                                  'YYYY-MM-DD') +
                                                          ROWNUM - 1 FDATE
                                                     FROM USER_OBJECTS
                                                    WHERE ROWNUM <= 11) TT
                                            WHERE TT.FDATE NOT IN
                                                  (SELECT FDATE
                                                     FROM TB_BASE_CHILDHOLIDAY
                                                    WHERE FHOLIDAYSCODE = 'CH'
                                                      AND FCHECKSTATE = 1
                                                      AND FDATE BETWEEN
                                                          TO_DATE('2008-12-21',
                                                                  'YYYY-MM-DD') AND
                                                          TO_DATE('2008-12-31',
                                                                  'YYYY-MM-DD')))
                                      AND FSETID IN ('3','4','5')) A
                            INNER JOIN (SELECT FPORTCODE
                                         FROM TB_002_PARA_PORTFOLIO
                                        WHERE FPORTCODE IN ('3','4','5')
                                          AND FPORTATTRIBUTE = '1') B ON A.FSETID =
                                                                         B.FPORTCODE)
                         ELSE
                          0
                       END ELSE(CASE
                                  WHEN SEASON = 1 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-03-21',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-03-21',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-03-31',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '1') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                  WHEN SEASON = 2 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-06-20',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-06-20',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-06-30',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '1') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                  ELSE
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-09-20',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-09-20',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-09-30',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '1') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                END) END) TOULIAN
                  FROM (SELECT ((CASE
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 0 THEN
                                  1
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 0 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 3 THEN
                                  2
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 3 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 6 THEN
                                  3
                                 ELSE
                                  4
                               END) - 1) AS SEASON
                          FROM DUAL)) AS TOULIAN,
               (SELECT (CASE
                         WHEN SEASON = 0 THEN
                          CASE
                         WHEN EXISTS
                          (SELECT *
                                 FROM USER_TABLES
                                WHERE TABLE_NAME = 'TB_002_2008_IMP_NAV') THEN
                          (SELECT SUM(NVL(FFUNDNAV, 0))
                             FROM (SELECT FSETID, FFUNDNAV
                                     FROM TB_002_2008_IMP_NAV
                                    WHERE FDATE IN
                                          (SELECT MAX(TT.FDATE) FDATE
                                             FROM (SELECT TO_DATE('2008-12-21',
                                                                  'YYYY-MM-DD') +
                                                          ROWNUM - 1 FDATE
                                                     FROM USER_OBJECTS
                                                    WHERE ROWNUM <= 11) TT
                                            WHERE TT.FDATE NOT IN
                                                  (SELECT FDATE
                                                     FROM TB_BASE_CHILDHOLIDAY
                                                    WHERE FHOLIDAYSCODE = 'CH'
                                                      AND FCHECKSTATE = 1
                                                      AND FDATE BETWEEN
                                                          TO_DATE('2008-12-21',
                                                                  'YYYY-MM-DD') AND
                                                          TO_DATE('2008-12-31',
                                                                  'YYYY-MM-DD')))
                                      AND FSETID IN ('3','4','5')) A
                            INNER JOIN (SELECT FPORTCODE
                                         FROM TB_002_PARA_PORTFOLIO
                                        WHERE FPORTCODE IN ('3','4','5')
                                          AND FPORTATTRIBUTE = '2') B ON A.FSETID =
                                                                         B.FPORTCODE)
                         ELSE
                          0
                       END ELSE(CASE
                                  WHEN SEASON = 1 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-03-21',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-03-21',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-03-31',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '2') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                  WHEN SEASON = 2 THEN
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-06-20',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-06-20',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-06-30',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '2') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                  ELSE
                                   (SELECT SUM(NVL(FFUNDNAV, 0))
                                      FROM (SELECT FSETID, FFUNDNAV
                                              FROM TB_002_2009_IMP_NAV
                                             WHERE FDATE IN
                                                   (SELECT MAX(TT.FDATE) FDATE
                                                      FROM (SELECT TO_DATE('2009-09-20',
                                                                           'YYYY-MM-DD') +
                                                                   ROWNUM - 1 FDATE
                                                              FROM USER_OBJECTS
                                                             WHERE ROWNUM <= 11) TT
                                                     WHERE TT.FDATE NOT IN
                                                           (SELECT FDATE
                                                              FROM TB_BASE_CHILDHOLIDAY
                                                             WHERE FHOLIDAYSCODE = 'CH'
                                                               AND FCHECKSTATE = 1
                                                               AND FDATE BETWEEN
                                                                   TO_DATE('2009-09-20',
                                                                           'YYYY-MM-DD') AND
                                                                   TO_DATE('2009-09-30',
                                                                           'YYYY-MM-DD')))
                                               AND FSETID IN ('3','4','5')) A
                                     INNER JOIN (SELECT FPORTCODE
                                                  FROM TB_002_PARA_PORTFOLIO
                                                 WHERE FPORTCODE IN ('3','4','5')
                                                   AND FPORTATTRIBUTE = '2') B ON A.FSETID =
                                                                                  B.FPORTCODE)
                                END) END) WANNENG
                  FROM (SELECT ((CASE
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 0 THEN
                                  1
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 0 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 3 THEN
                                  2
                                 WHEN TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 > 3 AND
                                      TO_NUMBER(TO_CHAR(TO_DATE('2009-08-20',
                                                                'YYYY-MM-DD'),
                                                        'MM')) - 3 <= 6 THEN
                                  3
                                 ELSE
                                  4
                               END) - 1) AS SEASON
                          FROM DUAL)) AS WANNENG
          FROM DUAL)
         
         
         
         
          --------------------------------------------------------
         
select * from Tb_002_Para_Portfolio where fportcode in ('3','4','5')
select * from TB_002_2009_IMP_NAV where fdate = TO_DATE('2009-06-30', 'YYYY-MM-DD') and fsetid in ('3','4','5')
update  Tb_002_Para_Portfolio set FPORTATTRIBUTE='1' where fportcode in ('4')
update  Tb_002_Para_Portfolio set FPORTATTRIBUTE='2' where fportcode in ('5')
select * from TB_BASE_CHILDHOLIDAY order by fdate desc

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值