具体SQL如下:
select M.SJDWBH AS SJDWBH,M.KB AS KB,M.CYDS AS CYDS,
M.JQ_QSFMT as JQ_QSFMT,
M.JQ_KQGZJSFMAD AS JQ_KQGZJSFMAD,
M.JQ_KQGZJHFAAD AS JQ_KQGZJHFAAD,
M.JQ_KQGZJHFFVAD AS JQ_KQGZJHFFVAD,
M.JQ_KQGZJQLST_AD AS JQ_KQGZJQLST_AD,
M.JQ_DTFRLQB_AD AS JQ_DTFRLQB_AD,
M.JQ_MAR AS JQ_MAR,
M.JQ_QHL AS JQ_QHL,
M.JQ_SDJHFAAR AS JQ_SDJHFAAR,
M.JQ_SDJHFFVAR AS JQ_SDJHFFVAR,
M.JQ_GZWHJHFFVDAF AS JQ_GZWHJHFFVDAF,
M.JQ_GZJQLST_D AS JQ_GZJQLST_D,
M.JQ_DWFRLQNET_AR AS JQ_DWFRLQNET_AR,
M.JQ_FRL_K AS JQ_FRL_K
from
(SELECT '-1' as SJDWBH,GYSXX.SJDWBH AS KB,sum(T.CYDS) as CYDS,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_Mar when ((select js from hyd where cydid=h.cydid)='02') then (select QSFMT from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else QSFMT end) * T.CYDS)/sum(T.CYDS),2)) as JQ_QSFMT,
decode(sum(T.CYDS),0,0, round(sum(H.KQGZJSFMAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJSFMAD,
decode(sum(T.CYDS),0,0, round(sum(H.KQGZJHFAAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFAAD,
decode(sum(T.CYDS),0,0, round(sum(H.KQGZJHFFVAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFFVAD,
decode(sum(T.CYDS),0,0, round(sum(H.KQGZJQLST_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJQLST_AD,
decode(sum(T.CYDS),0,0, round(sum(H.DTFRLQB_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_DTFRLQB_AD,
decode(sum(T.CYDS),0,0, round(sum(H.MAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_MAR,
decode(sum(T.CYDS),0,0, round(sum(H.QHL * T.CYDS)/sum(T.CYDS),2)) AS JQ_QHL,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_SDJHFAar when ((select js from hyd where cydid=h.cydid)='02') then (select SDJHFAar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else SDJHFAar end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFAAR,
decode(sum(T.CYDS),0,0, round(sum(H.SDJHFFVAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFFVAR,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_GZWHJHFFVdaf when ((select js from hyd where cydid=h.cydid)='02') then (select GZWHJHFFVdaf from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else GZWHJHFFVdaf end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZWHJHFFVDAF,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_GZJQLSt_d when ((select js from hyd where cydid=h.cydid)='02') then (select GZJQLSt_d from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else GZJQLSt_d end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZJQLST_D,
decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_DWFRLQnet_ar when ((select js from hyd where cydid=h.cydid)='02') then (select DWFRLQnet_ar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else DWFRLQnet_ar end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_DWFRLQNET_AR,
decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_FRL_K when ((select js from hyd where cydid=h.cydid)='02') then (select FRL_K from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else FRL_K end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_FRL_K
FROM HYD H, GYSXX,
(SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=qclm.cydid)='02') then jz_1 else pz end) AS CYDS,SUM(PZ) AS PiaoZ FROM QCLM WHERE TO_CHAR(QCLM.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
UNION
SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=TRAINCOMECOAL.cydid)='02') then jz else piaoz end) AS CYDS,SUM(PiaoZ) AS PiaoZ FROM TRAINCOMECOAL WHERE TO_CHAR(TRAINCOMECOAL.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
) T
WHERE H.CYDID=T.CYDID AND GYSXX.GYSBH = H.KB
Group BY GYSXX.SJDWBH
union
SELECT GYSXX.SJDWBH as SJDWBH,H.KB as kb,sum(T.CYDS) as CYDS,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_Mar when ((select js from hyd where cydid=h.cydid)='02') then (select QSFMT from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=H.fgeneralselfordersign) else QSFMT end) * T.CYDS)/sum(T.CYDS),2)) as JQ_QSFMT,
decode(sum(T.CYDS),0,0, round(sum(h.KQGZJSFMAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJSFMAD,
decode(sum(T.CYDS),0,0, round(sum(h.KQGZJHFAAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFAAD,
decode(sum(T.CYDS),0,0, round(sum(h.KQGZJHFFVAD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJHFFVAD,
decode(sum(T.CYDS),0,0, round(sum(h.KQGZJQLST_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_KQGZJQLST_AD,
decode(sum(T.CYDS),0,0, round(sum(h.DTFRLQB_AD * T.CYDS)/sum(T.CYDS),2)) AS JQ_DTFRLQB_AD,
decode(sum(T.CYDS),0,0, round(sum(h.MAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_MAR,
decode(sum(T.CYDS),0,0, round(sum(h.QHL * T.CYDS)/sum(T.CYDS),2)) AS JQ_QHL,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_SDJHFAar when ((select js from hyd where cydid=h.cydid)='02') then (select SDJHFAar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else SDJHFAar end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFAAR,
decode(sum(T.CYDS),0,0, round(sum(h.SDJHFFVAR * T.CYDS)/sum(T.CYDS),2)) AS JQ_SDJHFFVAR,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_GZWHJHFFVdaf when ((select js from hyd where cydid=h.cydid)='02') then (select GZWHJHFFVdaf from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else GZWHJHFFVdaf end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZWHJHFFVDAF,
decode(sum(T.CYDS),0,0, round(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_GZJQLSt_d when ((select js from hyd where cydid=h.cydid)='02') then (select GZJQLSt_d from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else GZJQLSt_d end) * T.CYDS)/sum(T.CYDS),2)) AS JQ_GZJQLST_D,
decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_DWFRLQnet_ar when ((select js from hyd where cydid=h.cydid)='02') then (select DWFRLQnet_ar from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else DWFRLQnet_ar end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_DWFRLQNET_AR,
decode(sum(T.CYDS),0,0, Trunc(sum((case when ((select js from hyd where cydid=h.cydid)='03') then KF_FRL_K when ((select js from hyd where cydid=h.cydid)='02') then (select FRL_K from hyd where FCHEMICALKIND='02' and fgeneralselfordersign=h.fgeneralselfordersign) else FRL_K end) * T.CYDS)/sum(T.CYDS),0)) AS JQ_FRL_K
FROM HYD h, GYSXX,
(SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=qclm.cydid)='02') then jz_1 else pz end) AS CYDS,SUM(PZ) AS PiaoZ FROM QCLM WHERE TO_CHAR(QCLM.RCSJ,'YYYY-MM') = '2011-01' GROUP BY CYDID
UNION
SELECT cydid,count(ID) AS CYCS,SUM(case when ((select jszl from hyd where cydid=TRAINCOMECOAL.cydid)='02') then jz else piaoz end) AS CYDS,SUM(PiaoZ) AS PiaoZ FROM TRAINCOMECOAL WHERE TO_CHAR(TRAINCOMECOAL.RCSJ,'YYYY-MM') ='2011-01' GROUP BY CYDID
) T
WHERE h.CYDID=T.CYDID AND GYSXX.GYSBH = h.KB
Group BY GYSXX.SJDWBH,h.KB
) M order by kb |