with base as --在较早的版本中,不支持在base后面定义字段名
(
.....
),
tbl1 as(
),
tbl2 as(
),
...
tbl6 as(
),
t as( --把t的定义写到后面来,别括在tbl1...6外面
......
)
select ...
from t...
......
(
.....
),
tbl1 as(
),
tbl2 as(
),
...
tbl6 as(
),
t as( --把t的定义写到后面来,别括在tbl1...6外面
......
)
select ...
from t...
......
WITH BASE(BASE_REC_ID,VLD_ENTERPRISE_ID) AS
(
SELECT RB.BASE_REC_ID,VE.VLD_ENTERPRISE_ID
FROM C_SUPERVISE_CHKSAFTY_RECB_TB RB
INNER JOIN V_VLD_SITE_ENTERPRISE VE ON RB.VLD_SITE_ID = VE.VLD_SITE_ID
INNER JOIN VLD_SITE ON RB.VLD_SITE_ID = VLD_SITE.VLD_SITE_ID
WHERE VLD_SITE.ORG_STATUS = 'A'
),
TBL1 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS NTB
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS='1' OR P.TREAT_STATUS='2' AND P.RESTRIC_TREAT_DATE<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL2 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS TBNV
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS='4' AND P.RESTRIC_TREAT_DATE<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL3 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS V
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS='5' AND P.RESTRIC_TREAT_DATE<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL4 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS TBING
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS='6' AND P.RESTRIC_TREAT_DATE<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL5 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS TBALL
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS<>'1' AND P.RESTRIC_TREAT_DATE<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL6 AS
(
SELECT RB.VLD_ENTERPRISE_ID,
COUNT(*) AS TBO
FROM C_SUPERVISE_CHK_PROBLEMLIST_TB P
INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB B
ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB R
ON B.REC_ID = R.REC_ID
LEFT JOIN C_SUPERVISE_CHK_TREATBACK_TB BK
ON P.PROBLEM_ID = BK.PROBLEM_ID AND B.TREAT_INFORM_ID = BK.TREAT_INFORM_ID
INNER JOIN BASE RB
ON R.BASE_REC_ID = RB.BASE_REC_ID
WHERE P.TREAT_STATUS='2' OR P.TREAT_STATUS='6' AND (CASE WHEN BK.IS_DELAY='1' THEN BK.DELAYTREATCOMLELTEDATE ELSE P.RESTRIC_TREAT_DATE END)<SYSDATE
GROUP BY RB.VLD_ENTERPRISE_ID
),
T AS (SELECT VS.VLD_SITE_ID AS SITEID,
VS.SITE_PIC_NAME AS ENTERPRISE_NAME,
nvl(TBL1.NTB,0) AS ALLNOT_CORRECT,
nvl(TBL2.TBNV,0) AS PROBLEM_NOT_VERIFIED,
nvl(TBL3.V,0) AS PROBLEM_VERIFIED,
nvl(TBL4.TBING,0) AS PROBLEM_ING,
nvl(TBL6.TBO,0) AS NOT_CORRECT,
CASE WHEN (nvl(TBL5.TBALL,0)=0 OR (nvl(TBL2.TBNV,0)+nvl(TBL3.V,0))=0) THEN 0 ELSE
round(((nvl(TBL2.TBNV,0)+nvl(TBL3.V,0)) / (nvl(TBL5.TBALL,0))*100),1)
END AS PERCENTAGE,
CASE WHEN nvl(TBL5.TBALL,0)=0 OR nvl(TBL3.V,0)=0 THEN 0 ELSE
round(((nvl(TBL3.V,0) / nvl(TBL5.TBALL,0))*100),1)
END AS V_PERCENTAGE
FROM VLD_SITE VS
LEFT JOIN TBL1 ON VS.VLD_SITE_ID=TBL1.VLD_ENTERPRISE_ID
LEFT JOIN TBL2 ON VS.VLD_SITE_ID=TBL2.VLD_ENTERPRISE_ID
LEFT JOIN TBL3 ON VS.VLD_SITE_ID=TBL3.VLD_ENTERPRISE_ID
LEFT JOIN TBL4 ON VS.VLD_SITE_ID=TBL4.VLD_ENTERPRISE_ID
LEFT JOIN TBL5 ON VS.VLD_SITE_ID=TBL5.VLD_ENTERPRISE_ID
LEFT JOIN TBL6 ON VS.VLD_SITE_ID=TBL6.VLD_ENTERPRISE_ID
WHERE VS.ORG_STATUS = 'A' AND VS.RENDER_ORDER < 999 AND VS.VLD_ENTITY_TYPE_ID=20)
SELECT ENTERPRISE_NAME AS "板块公司",PERCENTAGE AS "整改完成率",V_PERCENTAGE AS "验证率" FROM TSELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',(整改完成率)) AS 装备
FROM T
UNION ALL
SELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',整改完成率)) AS 装备
FROM T那这样写的话那我后面的行列转换应该怎么写,请指点一下,谢谢
这句换成你那下面的行列转换的语句就可以了,对应的字段名稍微调整下
SELECT 'PERCENTAGE' as 类别
MAX(DECODE(ENTERPRISE_NAME,'销售',PERCENTAGE)) AS 销售,
MAX(DECODE(ENTERPRISE_NAME,'勘探',PERCENTAGE)) AS 勘探,
MAX(DECODE(ENTERPRISE_NAME,'炼化',PERCENTAGE)) AS 炼化,
MAX(DECODE(ENTERPRISE_NAME,'工建',PERCENTAGE)) AS 工建,
MAX(DECODE(ENTERPRISE_NAME,'管道',PERCENTAGE)) AS 管道,
MAX(DECODE(ENTERPRISE_NAME,'工技',PERCENTAGE)) AS 工技,
MAX(DECODE(ENTERPRISE_NAME,'海外',PERCENTAGE)) AS 海外,
MAX(DECODE(ENTERPRISE_NAME,'222',PERCENTAGE)) AS "222",
MAX(DECODE(ENTERPRISE_NAME,'其他',PERCENTAGE)) AS 其他,
MAX(DECODE(ENTERPRISE_NAME,'科研',PERCENTAGE)) AS 科研,
MAX(DECODE(ENTERPRISE_NAME,'1231',PERCENTAGE)) AS "1231",
MAX(DECODE(ENTERPRISE_NAME,'装备',(PERCENTAGE)) AS 装备
FROM T
但是为什么说找不到from关键字呢
SELECT 'PERCENTAGE' as 类别
最后少个逗号