SQL小问题

下面的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)









评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值