救命啊 询问一个sql查询的问题

Question

如图 想要使金水区下的营业厅里的数值通过sum和金水区的合并到一起 如果其它营业部有数值的话 比如二七区 也让下面的营业厅的数值合并 下面营业厅和营业部的关系是 营业厅的PARENT_ORG_ID是营业部的ORG_ID 请问这种情况如何sum？
这样写的话不能得到完整的值，主要是别的字段不一样 组织代码和组织名称 如果不要那两个字段就可以实现 但前台又无法取值了 请问有什么办法能让它们的值合并？

Answer 1

SELECT RO.CITY_ID, RAS_PUBLIC_PKG.GET_ORG_NAME(RO.CITY_ID) CITY_NAME, RO.ORG_CODE, RO.NAME,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'HANDSET',DECODE(RH.AUDIT_STATUS,'APPROVED',1,0),0)) H_APPROVED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'HANDSET',DECODE(RH.STATUS,'IN_COMPLETE',1,'COMPLETED',NVL2(RH.AUDIT_STATUS,0,1),0),0)) H_IN_COMPLETE,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'HANDSET',DECODE(RH.AUDIT_STATUS,'COMPLETED',1,0),0)) H_COMPLETED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'HANDSET',DECODE(RH.STATUS,'COMPLETED',DECODE(RH.AUDIT_STATUS,'REJECTED',1,'IN_COMPLETE',1,0),0),0)) H_REJECTED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'SIM',DECODE(RH.AUDIT_STATUS,'APPROVED',1,0),0)) S_APPROVED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'SIM',DECODE(RH.STATUS,'IN_COMPLETE',1,'COMPLETED',NVL2(RH.AUDIT_STATUS,0,1),0),0)) S_IN_COMPLETE,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'SIM',DECODE(RH.AUDIT_STATUS,'COMPLETED',1,0),0)) S_COMPLETED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'SIM',DECODE(RH.STATUS,'COMPLETED',DECODE(RH.AUDIT_STATUS,'REJECTED',1,'IN_COMPLETE',1,0),0),0)) S_REJECTED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'PROMOTION',DECODE(RH.AUDIT_STATUS,'APPROVED',1,0),0)) P_APPROVED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'PROMOTION',DECODE(RH.STATUS,'IN_COMPLETE',1,'COMPLETED',NVL2(RH.AUDIT_STATUS,0,1),0),0)) P_IN_COMPLETE,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'PROMOTION',DECODE(RH.AUDIT_STATUS,'COMPLETED',1,0),0)) P_COMPLETED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'PROMOTION',DECODE(RH.STATUS,'COMPLETED',DECODE(RH.AUDIT_STATUS,'REJECTED',1,'IN_COMPLETE',1,0),0),0)) P_REJECTED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD',DECODE(RH.AUDIT_STATUS,'APPROVED',1,0),0)) V_APPROVED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD',DECODE(RH.STATUS,'IN_COMPLETE',1,'COMPLETED',NVL2(RH.AUDIT_STATUS,0,1),0),0)) V_IN_COMPLETE,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD',DECODE(RH.AUDIT_STATUS,'COMPLETED',1,0),0)) V_COMPLETED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD',DECODE(RH.STATUS,'COMPLETED',DECODE(RH.AUDIT_STATUS,'REJECTED',1,'IN_COMPLETE',1,0),0),0)) V_REJECTED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD_OLD',DECODE(RH.AUDIT_STATUS,'APPROVED',1,0),0)) O_APPROVED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD_OLD',DECODE(RH.STATUS,'IN_COMPLETE',1,'COMPLETED',NVL2(RH.AUDIT_STATUS,0,1),0),0)) O_IN_COMPLETE,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD_OLD',DECODE(RH.AUDIT_STATUS,'COMPLETED',1,0),0)) O_COMPLETED,
 SUM(DECODE(RH.DOCUMENT_TYPE_CODE,'VALUECARD_OLD',DECODE(RH.STATUS,'COMPLETED',DECODE(RH.AUDIT_STATUS,'REJECTED',1,'IN_COMPLETE',1,0),0),0)) O_REJECTED
  FROM RAS_DAILY_RPT_ENTER_HEADERS RH,  (SELECT * FROM RAS_ORGANIZATIONS RO 
  WHERE RO.CITY_ID = 100001 )  RO  WHERE RH.STATUS <> 'CANCELED'
 AND RO.ORG_TYPE <> 'ORG_PROVINCE'
 AND RO.ORG_TYPE <> 'ORG_CITY'
   AND RH.REVENUE_ORG_ID = RO.ORG_ID 
   AND RH.DISABLED <> 'Y'
   AND (2007-01-01 IS NULL OR RH.REVENUE_START_DATE >= TO_DATE('2007-01-01','YYYY-MM-DD'))
   AND (2012-01-31 IS NULL OR RH.REVENUE_START_DATE < TO_DATE('2012-01-31','YYYY-MM-DD')+1)
   GROUP BY RO.CITY_ID,RO.ORG_CODE,RO.NAME
总之就是那几个字段使用使用sum能够合并成一行 最后的结果就是金水区底下的营业厅和金水区的数值是一行二七区和二七区底下的数值是一行 它们之间有PARENT_ORG_ID的关系

Answer 2

鄭州的吧？
按你的說話，應該還有一個表吧？記錄PARENT_ORG_ID 和 ORG_ID 的關係的。