SQL小问题

来源：互联网发布：算法设计与分析以acm 编辑：程序博客网时间：2024/05/16 10:43

下面的SQL注意的是ROUND(CNT/SUMCNT * 100,2) PER_CNT这样写,不要写成ROUND(CNT/SUMCNT,2) * 100 PER_CNT

SELECT MONTH_TIME,WATER_TYPE,CNT,ROUND(CNT/SUMCNT * 100,2) PER_CNT FROM (

SELECT (SELECT YEAR(SYSDATE)||'年'||(SELECT RIGHT(TO_CHAR(MAX(WQ_INF_TIME),'YYYY-MM'),2)||'月' FROM JCZZ.INI_WATQUALITY_INFO A
LEFT JOIN JCZZ.BAS_WATERQUALITY_PIONTINFO B ON A.WQ_PI_CODE=B.WQ_PI_CODE WHERE B.WQ_PI_CODE IN (SELECT
A.WQ_PI_CODE
FROM JCZZ.BAS_WATERQUALITY_PIONTINFO A
LEFT JOIN JCZZ.COD_WATFUNCTION B ON A.WQ_PI_FUNCTION=B.PF_CODE
WHERE
A.WQ_PI_FUNCTION IS NOT NULL AND
A.WQ_PI_FUNCTION != '02')) FROM DUAL
) MONTH_TIME,WATER_TYPE,COUNT(WATER_TYPE) CNT,(
SELECT COUNT(WATER_TYPE) CNT FROM JCZZ.INI_WATQUALITY_INFO A
LEFT JOIN JCZZ.BAS_WATERQUALITY_PIONTINFO B ON A.WQ_PI_CODE=B.WQ_PI_CODE WHERE
B.WQ_PI_CODE IN (SELECT
A.WQ_PI_CODE
FROM JCZZ.BAS_WATERQUALITY_PIONTINFO A
LEFT JOIN JCZZ.COD_WATFUNCTION B ON A.WQ_PI_FUNCTION=B.PF_CODE
WHERE A.WQ_PI_FUNCTION IS NOT NULL AND A.WQ_PI_FUNCTION != '02') AND
TO_CHAR(WQ_INF_TIME,'YYYY-MM')=
(SELECT TO_CHAR( MAX(WQ_INF_TIME),'YYYY-MM') FROM JCZZ.INI_WATQUALITY_INFO ) AND
WATER_TYPE IN ('Ⅰ','Ⅰ类','Ⅲ','Ⅲ类','Ⅱ','Ⅱ类','Ⅳ','Ⅳ类','Ⅴ','Ⅴ类','劣Ⅴ','劣Ⅴ类')
) SUMCNT
FROM JCZZ.INI_WATQUALITY_INFO A
LEFT JOIN JCZZ.BAS_WATERQUALITY_PIONTINFO B ON A.WQ_PI_CODE=B.WQ_PI_CODE WHERE
B.WQ_PI_CODE IN (SELECT
A.WQ_PI_CODE
FROM JCZZ.BAS_WATERQUALITY_PIONTINFO A
LEFT JOIN JCZZ.COD_WATFUNCTION B ON A.WQ_PI_FUNCTION=B.PF_CODE
WHERE A.WQ_PI_FUNCTION IS NOT NULL AND A.WQ_PI_FUNCTION != '02') AND
TO_CHAR(WQ_INF_TIME,'YYYY-MM')=
(SELECT TO_CHAR( MAX(WQ_INF_TIME),'YYYY-MM') FROM JCZZ.INI_WATQUALITY_INFO ) AND
WATER_TYPE IN ('Ⅰ','Ⅰ类','Ⅲ','Ⅲ类','Ⅱ','Ⅱ类','Ⅳ','Ⅳ类','Ⅴ','Ⅴ类','劣Ⅴ','劣Ⅴ类') GROUP BY
WATER_TYPE)

阅读全文

0 0