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
写过最长的一次sql
最新推荐文章于 2022-08-23 11:00:54 发布