我原来查出来的是这样的: TJL 8 我现在需要的是这样: MAT_NAME TJL AA 8 MAT_NAME这个字段是H_BASICCONFIG_CHEMICAL_MAIN_TM 这个表里面的,就是上面标红的那个,如果按MAT_NAME分区统计应该怎么改,能达到我要的那种效果么
按现有语句的话就一层层往外加呗,不过咋说呢,这语句写得,优化空间大大地…… SELECT MAT_NAME,TJL FROM (SELECT FZ.MAT_NAME,DECODE(NVL(FM.JHZS, 0), 0, 0, ROUND(NVL(FZ.JGZS, 0) / NVL(FM.JHZS, 0) * 100, 2)) AS TJL FROM (SELECT DETAIL_COUNT + DETAIL_COUNT1 AS JHZS FROM (SELECT NVL(COUNT(1), 0) AS DETAIL_COUNT FROM (SELECT P1.STAFF_MAIN_ID FROM H_EXAM_PLAN_MAIN_TB T INNER JOIN H_EXAM_PLAN_PERSON_DTL_TB P1 ON T.PLAN_MAIN_ID = P1.PLAN_MAIN_ID INNER JOIN H_EXAM_PLAN_STAFFOC_TB T1 ON P1.PLAN_INC_PERDETL_ID = T1.PLAN_INC_PERDETL_ID INNER JOIN H_BASICCONFIG_CHEMICAL_MAIN_TM T2 ON T2.CHEMICAL_MAIN_ID = T1.CHEMICAL_MAIN_ID INNER JOIN (SELECT * FROM VLD_SITE CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID START WITH VLD_SITE_ID = 10) VS ON T.VLD_SITE_ID = VS.VLD_SITE_ID WHERE T.EXAM_YEAR = to_char('2012' ) AND T2.OCRFACTORS_TYPE = 4 AND VS.ORG_STATUS = 'A' AND T.IS_FINISH = '1' GROUP BY P1.STAFF_MAIN_ID)), (SELECT NVL(SUM('GW'), 0) AS DETAIL_COUNT1 FROM H_EXAM_PLAN_MAIN_TB T INNER JOIN H_EXAM_PLAN_PERSON_NUM_TB P2 ON T.PLAN_MAIN_ID = P2.PLAN_MAIN_ID INNER JOIN (SELECT * FROM VLD_SITE CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID START WITH VLD_SITE_ID = 10) VS ON T.VLD_SITE_ID = VS.VLD_SITE_ID WHERE T.EXAM_YEAR = to_char('2012') AND VS.ORG_STATUS = 'A' AND T.IS_FINISH = '1') ) FM, (SELECT COUNT(1) AS JGZS,MAX(MAT_NAME) MAT_NAME FROM (SELECT R1.STAFF_MAIN_ID,max(T2.MAT_NAME) MAT_NAME FROM H_EXAM_RESULT_MAIN_TB R INNER JOIN H_EXAM_RESULT_DETAIL_TB R1 ON R.RESULT_MAIN_ID = R1.RESULT_MAIN_ID INNER JOIN (SELECT * FROM VLD_SITE CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID START WITH VLD_SITE_ID = 10) VS ON R.VLD_SITE_ID = VS.VLD_SITE_ID LEFT JOIN H_EXAM_RESULT_DTL_FACT_TB R2 ON R2.RESULT_DETAIL_ID = R1.RESULT_DETAIL_ID LEFT JOIN H_BASICCONFIG_CHEMICAL_MAIN_TM T2 ON R2.CHEMICAL_MAIN_ID = T2.CHEMICAL_MAIN_ID WHERE R1.EXAM_RESULT IS NOT NULL AND VS.ORG_STATUS = 'A' AND R.EXAM_YEAR = to_char('2012') AND T2.OCRFACTORS_TYPE = 4 GROUP BY R1.STAFF_MAIN_ID) ) FZ)
TJL
8
我现在需要的是这样:
MAT_NAME TJL
AA 8
MAT_NAME这个字段是H_BASICCONFIG_CHEMICAL_MAIN_TM 这个表里面的,就是上面标红的那个,如果按MAT_NAME分区统计应该怎么改,能达到我要的那种效果么
SELECT MAT_NAME,TJL FROM
(SELECT FZ.MAT_NAME,DECODE(NVL(FM.JHZS, 0),
0,
0,
ROUND(NVL(FZ.JGZS, 0) / NVL(FM.JHZS, 0) * 100, 2)) AS TJL
FROM (SELECT DETAIL_COUNT + DETAIL_COUNT1 AS JHZS
FROM (SELECT NVL(COUNT(1), 0) AS DETAIL_COUNT
FROM (SELECT P1.STAFF_MAIN_ID
FROM H_EXAM_PLAN_MAIN_TB T
INNER JOIN H_EXAM_PLAN_PERSON_DTL_TB P1
ON T.PLAN_MAIN_ID = P1.PLAN_MAIN_ID
INNER JOIN H_EXAM_PLAN_STAFFOC_TB T1
ON P1.PLAN_INC_PERDETL_ID = T1.PLAN_INC_PERDETL_ID
INNER JOIN H_BASICCONFIG_CHEMICAL_MAIN_TM T2
ON T2.CHEMICAL_MAIN_ID = T1.CHEMICAL_MAIN_ID
INNER JOIN (SELECT *
FROM VLD_SITE
CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID
START WITH VLD_SITE_ID = 10) VS
ON T.VLD_SITE_ID = VS.VLD_SITE_ID
WHERE T.EXAM_YEAR = to_char('2012' )
AND T2.OCRFACTORS_TYPE = 4
AND VS.ORG_STATUS = 'A'
AND T.IS_FINISH = '1'
GROUP BY P1.STAFF_MAIN_ID)),
(SELECT NVL(SUM('GW'), 0) AS DETAIL_COUNT1
FROM H_EXAM_PLAN_MAIN_TB T
INNER JOIN H_EXAM_PLAN_PERSON_NUM_TB P2
ON T.PLAN_MAIN_ID = P2.PLAN_MAIN_ID
INNER JOIN (SELECT *
FROM VLD_SITE
CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID
START WITH VLD_SITE_ID = 10) VS
ON T.VLD_SITE_ID = VS.VLD_SITE_ID
WHERE T.EXAM_YEAR = to_char('2012')
AND VS.ORG_STATUS = 'A'
AND T.IS_FINISH = '1')
) FM,
(SELECT COUNT(1) AS JGZS,MAX(MAT_NAME) MAT_NAME
FROM (SELECT R1.STAFF_MAIN_ID,max(T2.MAT_NAME) MAT_NAME
FROM H_EXAM_RESULT_MAIN_TB R
INNER JOIN H_EXAM_RESULT_DETAIL_TB R1
ON R.RESULT_MAIN_ID = R1.RESULT_MAIN_ID
INNER JOIN (SELECT *
FROM VLD_SITE
CONNECT BY PRIOR VLD_SITE_ID = VLD_PARENT_SITE_ID
START WITH VLD_SITE_ID = 10) VS
ON R.VLD_SITE_ID = VS.VLD_SITE_ID
LEFT JOIN H_EXAM_RESULT_DTL_FACT_TB R2
ON R2.RESULT_DETAIL_ID = R1.RESULT_DETAIL_ID
LEFT JOIN H_BASICCONFIG_CHEMICAL_MAIN_TM T2
ON R2.CHEMICAL_MAIN_ID = T2.CHEMICAL_MAIN_ID
WHERE R1.EXAM_RESULT IS NOT NULL
AND VS.ORG_STATUS = 'A'
AND R.EXAM_YEAR = to_char('2012')
AND T2.OCRFACTORS_TYPE = 4
GROUP BY R1.STAFF_MAIN_ID)
) FZ)
我也没办法,临时接过来的,开始也不是我写的,以前也没接触过Oracle,但还是谢谢你了
select '高温',TJL FROM (
……--你的语句
)