日期:2014-05-17 浏览次数:20925 次
SELECT tb2.*--,rank()over(ORDER BY TB2.DCL DESC) 排序 FROM ( SELECT TB1.*, CASE WHEN TB1.PLANVALUE IS NULL OR TB1.PLANVALUE = 0 THEN 0 ELSE TB1.MONTH_UW_POSITIVEAPE / TB1.PLANVALUE END DCL FROM ( SELECT F.DATEID YEARMONTHID, substr(F.MNGCOMID, 1, 4) MNGCOMID, SUM(F.MONTH_UW_POSITIVEAPE) MONTH_UW_POSITIVEAPE, C.PLANVALUE * 10000 PLANVALUE From FACT_RISK_DAILY_INCOME F FULL JOIN ods_user.CONFIG_T2COMPLAN_MONTH C ON SUBSTR(MNGCOMID, 1, 4) = C.T2ID and substr(F.DATEID, 1, 6) = C.YEARMONTHID WHERE C.INDEXID = 'Q10101' GROUP BY F.DATEID, substr(F.MNGCOMID, 1, 4), C.PLANVALUE) TB1) TB2
YEARMONTHID MNGCOMID MONTH_UW_POSITIVEAPE PLANVALUE DCL 20110220 8641 107749.7 318000 0.338835534591195 20110226 8641 196150.3 318000 0.616824842767296 20110228 8641 349070.95 318000 1.09770738993711 20110312 8641 34995.05 371000 0.0943262803234501 20110403 8641 4845.3 512628.6 0.00945187217412372 20110408 8641 50618.6 512628.6 0.0987432226762221 20110412 8641 61334.9 512628.6 0.119647830807723 20110418 8641 159853.4 512628.6 0.311830826450182 20110419 8641 161892.4 512628.6 0.315808364964421 20110421 8641 162517.5 512628.6 0.317027766300983 20110427 8641 204134.2 512628.6 0.3982107123949
YEARMONTHID MNGCOMID MONTH_UW_POSITIVEAPE PLANVALUE DCL 排序 390000 1 2370000 1 3610000 1 2190000 1 730000 1 -- 还有一些不打了 -- 在线等结果 希望高手告诉是什么原因造成的这样的结果 最好把正确的SQL些出来 谢谢
row_number()over(ORDER BY TB2.DCL DESC)