select decode(grouping(gtpfw_dwid), 1,999999999999, gtpfw_dwid) gtpfw_dwid_a /*单位ID*/,decode(grouping(ORGANIZATION_NAME), 1, '合计', ORGANIZATION_NAME) ORGANIZATION_NAME/*单位名称*/,
/**-------------------灰渣偏差率(%)--------------------------------**/
round(decode(sum(nvl(GTPFW_YJHSL, 0) /*Ⅰ级灰数量(吨)*/
+nvl(GTPFW_EJHSL, 0) /*Ⅱ级灰数量(吨)*/
+nvl(GTPFW_SJHSL, 0) /*Ⅲ级灰数量(吨)*/
+nvl(GTPFW_XHSL, 0) /*细灰数量(吨)*/
+nvl(GTPFW_MXHSL, 0) /*磨细灰数量(吨)*/
+nvl(GTPFW_FXXHSL, 0) /*分选细灰数量(吨)*/
+nvl(GTPFW_YHSL, 0) /*原灰数量(吨)*/
+nvl(GTPFW_CHSL, 0) /*粗灰数量(吨)*/
+nvl(GTPFW_SHSL, 0) /*湿灰数量(吨)*/
+nvl(GTPFW_CZLFMHSL, 0) /*处置量-粉煤灰数量(吨)*/
+nvl(GTPFW_LZSL, 0) /*炉渣数量(吨)*/
+nvl(GTPFW_CZLLZSL, 0) /*处置量-炉渣数量(吨)*/
+nvl(GTPFW_SGSL, 0) /*石膏数量(吨)*/
+nvl(GTPFW_SZMSL, 0) /*石子煤数量(吨)*/
+nvl(GTPFW_CZLSGSL, 0) /*处置量-石膏数量(吨)*/),
0,
0,
decode(hzhj /*灰渣量=灰分*燃煤量*/,
0,
0,
((sum(nvl(GTPFW_YJHSL, 0) /*Ⅰ级灰数量(吨)*/
+nvl(GTPFW_EJHSL, 0) /*Ⅱ级灰数量(吨)*/
+nvl(GTPFW_SJHSL, 0) /*Ⅲ级灰数量(吨)*/
+nvl(GTPFW_XHSL, 0) /*细灰数量(吨)*/
+nvl(GTPFW_MXHSL, 0) /*磨细灰数量(吨)*/
+nvl(GTPFW_FXXHSL, 0) /*分选细灰数量(吨)*/
+nvl(GTPFW_YHSL, 0) /*原灰数量(吨)*/
+nvl(GTPFW_CHSL, 0) /*粗灰数量(吨)*/
+nvl(GTPFW_SHSL, 0) /*湿灰数量(吨)*/
+nvl(GTPFW_CZLFMHSL, 0) /*处置量-粉煤灰数量(吨)*/
+nvl(GTPFW_LZSL, 0) /*炉渣数量(吨)*/
+nvl(GTPFW_CZLLZSL, 0) /*处置量-炉渣数量(吨)*/) -
hzhj /*灰渣量=灰分*燃量*/ ) /
hzhj /*灰渣量=灰分*燃煤量*/) * 100)),
2) hzpcl_by /* 灰渣偏差率*/
from tz_sjtb_gtpfw,
(select scrb_dwid, sum(t.scrb_rml/*燃煤量*/*t.scrb_hf /*灰分*/*0.01) hzhj
from tz_scrb_info t
where to_char(t.scrb_rq, 'yyyy-mm-dd') >= '2013-01-26'
and to_char(t.scrb_rq, 'yyyy-mm-dd') <= '2013-02-25' group by scrb_dwid) /*生产表*/,
PT_ORGANIZATION /*单位表*/,
TZ_KH_INFO /*客户基本信息表*/
where GTPFW_DWID = SCRB_DWID(+)
and ORGANIZATION_ID = GTPFW_DWID
and KH_ID = GTPFW_KHMC
and to_char(GTPFW_TBRQ, 'yyyy-mm-dd') >= '2013-01-26'
and to_char(GTPFW_TBRQ, 'yyyy-mm-dd') <= '2013-02-25'
group by rollup((gtpfw_dwid,ORGANIZATION_NAME,hzhj
))
order by gtpfw_dwid asc
目前结果是这样的(此结果无合计值):
2 陡河 -19.03
3 潮州 -3.98
4 大坝 1.74
5 丰润 0.44
999999999999 合计
想要的结果是:
2 陡河 -19.03
3 潮州 -3.98
4 大坝 1.74
5 丰润 0.44
999999999999 合计 -20.83
/**-------------------灰渣偏差率(%)--------------------------------**/
round(decode(sum(nvl(GTPFW_YJHSL, 0) /*Ⅰ级灰数量(吨)*/
+nvl(GTPFW_EJHSL, 0) /*Ⅱ级灰数量(吨)*/
+nvl(GTPFW_SJHSL, 0) /*Ⅲ级灰数量(吨)*/
+nvl(GTPFW_XHSL, 0) /*细灰数量(吨)*/
+nvl(GTPFW_MXHSL, 0) /*磨细灰数量(吨)*/
+nvl(GTPFW_FXXHSL, 0) /*分选细灰数量(吨)*/
+nvl(GTPFW_YHSL, 0) /*原灰数量(吨)*/
+nvl(GTPFW_CHSL, 0) /*粗灰数量(吨)*/
+nvl(GTPFW_SHSL, 0) /*湿灰数量(吨)*/
+nvl(GTPFW_CZLFMHSL, 0) /*处置量-粉煤灰数量(吨)*/
+nvl(GTPFW_LZSL, 0) /*炉渣数量(吨)*/
+nvl(GTPFW_CZLLZSL, 0) /*处置量-炉渣数量(吨)*/
+nvl(GTPFW_SGSL, 0) /*石膏数量(吨)*/
+nvl(GTPFW_SZMSL, 0) /*石子煤数量(吨)*/
+nvl(GTPFW_CZLSGSL, 0) /*处置量-石膏数量(吨)*/),
0,
0,
decode(hzhj /*灰渣量=灰分*燃煤量*/,
0,
0,
((sum(nvl(GTPFW_YJHSL, 0) /*Ⅰ级灰数量(吨)*/
+nvl(GTPFW_EJHSL, 0) /*Ⅱ级灰数量(吨)*/
+nvl(GTPFW_SJHSL, 0) /*Ⅲ级灰数量(吨)*/
+nvl(GTPFW_XHSL, 0) /*细灰数量(吨)*/
+nvl(GTPFW_MXHSL, 0) /*磨细灰数量(吨)*/
+nvl(GTPFW_FXXHSL, 0) /*分选细灰数量(吨)*/
+nvl(GTPFW_YHSL, 0) /*原灰数量(吨)*/
+nvl(GTPFW_CHSL, 0) /*粗灰数量(吨)*/
+nvl(GTPFW_SHSL, 0) /*湿灰数量(吨)*/
+nvl(GTPFW_CZLFMHSL, 0) /*处置量-粉煤灰数量(吨)*/
+nvl(GTPFW_LZSL, 0) /*炉渣数量(吨)*/
+nvl(GTPFW_CZLLZSL, 0) /*处置量-炉渣数量(吨)*/) -
hzhj /*灰渣量=灰分*燃量*/ ) /
hzhj /*灰渣量=灰分*燃煤量*/) * 100)),
2) hzpcl_by /* 灰渣偏差率*/
from tz_sjtb_gtpfw,
(select scrb_dwid, sum(t.scrb_rml/*燃煤量*/*t.scrb_hf /*灰分*/*0.01) hzhj
from tz_scrb_info t
where to_char(t.scrb_rq, 'yyyy-mm-dd') >= '2013-01-26'
and to_char(t.scrb_rq, 'yyyy-mm-dd') <= '2013-02-25' group by scrb_dwid) /*生产表*/,
PT_ORGANIZATION /*单位表*/,
TZ_KH_INFO /*客户基本信息表*/
where GTPFW_DWID = SCRB_DWID(+)
and ORGANIZATION_ID = GTPFW_DWID
and KH_ID = GTPFW_KHMC
and to_char(GTPFW_TBRQ, 'yyyy-mm-dd') >= '2013-01-26'
and to_char(GTPFW_TBRQ, 'yyyy-mm-dd') <= '2013-02-25'
group by rollup((gtpfw_dwid,ORGANIZATION_NAME,hzhj
))
order by gtpfw_dwid asc
目前结果是这样的(此结果无合计值):
2 陡河 -19.03
3 潮州 -3.98
4 大坝 1.74
5 丰润 0.44
999999999999 合计
想要的结果是:
2 陡河 -19.03
3 潮州 -3.98
4 大坝 1.74
5 丰润 0.44
999999999999 合计 -20.83
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货