--向以下这样的子查询在一个SQL里出现好几次类似的。。也是比较正常的。。 所以请教达人 有没有更高效的 写法 下面我贴些 现在我这里用的一些 完整的SQL请教达人 有哪些地方 还可以优化 SELECT A.DM AS FD00221_N_1C_60, NVL(SW.LAYER_NAME,'FD00094') AS FD00220_Y_1, (B.BQ_C1+B.BQ_C2+B.BQ_C3+B.BQ_C4) AS FD00346_Y_3_80, (A.C1+A.C2+A.C3+A.C4) AS FD00347_Y_3_80, (B.ZZL_C1+B.ZZL_C2+B.ZZL_C3++B.ZZL_C4) AS FD00096_Y_80, B.BQ_C1 AS FD00348_Y_3_80, A.C1 AS FD00349_Y_3_80, B.ZZL_C1 AS FD00350_Y_6_80, B.BQ_C2 AS FD00351_Y_3_80, A.C2 AS FD00352_Y_3_80, B.ZZL_C2 AS FD00353_Y_6_80, B.BQ_C3 AS FD00354_Y_3_80, A.C3 AS FD00355_Y_3_80, B.ZZL_C3 AS FD00356_Y_6_80, B.BQ_C4 AS FD00357_Y_3_80, A.C4 AS FD00358_Y_3_80, B.ZZL_C4 AS FD00359_Y_6_80FROM ( --各类型企业新增户数 SELECT T1.DM,NVL(SUM(T1.C1+T1.C5),0) AS C1,NVL(SUM(T1.C2),0) AS C2,NVL(SUM(T1.C3),0) AS C3,NVL(SUM(T1.C4),0) AS C4 FROM ( SELECT T.DM AS DM, DECODE(T.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1, DECODE(T.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2, DECODE(T.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3, DECODE(T.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4, DECODE(T.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5 FROM ( SELECT TAB.DM AS DM,TAB.CPCODE AS CPCODE,TAB.QYLX AS QYLX,TAB.CPTY AS CPTY FROM ( SELECT T1.DM AS DM,T1.CPCODE AS CPCODE,T1.QYLX AS QYLX,NVL(T2.CPCODE,0) AS CPTY FROM ( SELECT DISTINCT SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM, T.CPCODE AS CPCODE, NSR.QYLX AS QYLX FROM TS_JG_RDCP T,TS_CPCODE NSR WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200901' AND T.SB_YM<='200906' )T1 LEFT JOIN ( SELECT DISTINCT SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM, T.CPCODE AS CPCODE, NSR.QYLX AS QYLX FROM TS_JG_RDCP T,TS_CPCODE NSR WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200801' AND T.SB_YM<='200806' )T2 ON T1.DM=T2.DM AND T1.CPCODE=T2.CPCODE AND T1.QYLX=T2.QYLX )TAB WHERE TAB.CPTY=0 ) T GROUP BY T.DM,T.QYLX )T1 GROUP BY T1.DM )A, ( --各类型企业总户数 SELECT T1.DM, NVL(SUM(T1.C1+T1.C5),0) AS BQ_C1, NVL(SUM(T1.C2),0) AS BQ_C2, NVL(SUM(T1.C3),0) AS BQ_C3, NVL(SUM(T1.C4),0) AS BQ_C4, NVL(SUM(T2.C1+T2.C5),0) AS DBQ_C1, NVL(SUM(T2.C2),0) AS DBQ_C2, NVL(SUM(T2.C3),0) AS DBQ_C3, NVL(SUM(T2.C4),0) AS DBQ_C4, ((SUM(T1.C1+T1.C5)-SUM(T2.C1+T2.C5))/SUM(T2.C1++T2.C5)) AS ZZL_C1, ((SUM(T1.C2)-SUM(T2.C2))/SUM(T2.C2)) AS ZZL_C2, ((SUM(T1.C3)-SUM(T2.C3))/SUM(T2.C3)) AS ZZL_C3, ((SUM(T1.C4)-SUM(T2.C4))/SUM(T2.C4)) AS ZZL_C4 FROM ( SELECT SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM, DECODE(NSR.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1, DECODE(NSR.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2, DECODE(NSR.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3, DECODE(NSR.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4, DECODE(NSR.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5 FROM TS_JG_RDCP T,TS_CPCODE NSR WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200901' AND T.SB_YM<='200906' GROUP BY SUBSTR(NSR.NSR_SWJG_DM,0,7),NSR.QYLX )T1, ( SELECT SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM, DECODE(NSR.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1, DECODE(NSR.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2, DECODE(NSR.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3, DECODE(NSR.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4, DECODE(NSR.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5 FROM TS_JG_RDCP T,TS_CPCODE NSR WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200801' AND T.SB_YM<='200806' GROUP BY SUBSTR(NSR.NSR_SWJG_DM,0,7),NSR.QYLX )T2 where T1.dm=T2.dm group by T1.dm)B LEFT JOIN T01_SWJG SW ON B.DM=SUBSTR(SW.SWJG_DM,0,7) AND LAYER='2' WHERE A.DM=B.DM
select code AS 代码, (nvl(sum_2009, 0) - nvl(sum_2008, 0)) / sum_2008 AS 增长率, sum_2009 / sum_total AS 占比 from (select code, sum(case when DATE >= '200901' AND DATE <= '200912' then amnt end) sum_2009, sum(case when DATE >= '200801' AND DATE <= '200812' then amnt end) sum_2008, sum(amnt) sum_total FROM TAB WHERE DATE >= '200801' AND DATE <= '200912' GROUP BY CODE)
PSQL里 我可以这样写SELECT ID,SUM(RMB) FROM TAB GROUP BY ID WITH CUBE得出来的值最后是有个汇总合计的但是在PSQL里面 这样的写法 就是不支持的那请问达人 在PSQL里 代替WITH CUBE 这个函数的函数是什么
--向以下这样的子查询在一个SQL里出现好几次类似的。。也是比较正常的。。
所以请教达人 有没有更高效的
写法
下面我贴些 现在我这里用的一些 完整的SQL请教达人 有哪些地方 还可以优化
SELECT
A.DM AS FD00221_N_1C_60,
NVL(SW.LAYER_NAME,'FD00094') AS FD00220_Y_1,
(B.BQ_C1+B.BQ_C2+B.BQ_C3+B.BQ_C4) AS FD00346_Y_3_80,
(A.C1+A.C2+A.C3+A.C4) AS FD00347_Y_3_80,
(B.ZZL_C1+B.ZZL_C2+B.ZZL_C3++B.ZZL_C4) AS FD00096_Y_80,
B.BQ_C1 AS FD00348_Y_3_80,
A.C1 AS FD00349_Y_3_80,
B.ZZL_C1 AS FD00350_Y_6_80,
B.BQ_C2 AS FD00351_Y_3_80,
A.C2 AS FD00352_Y_3_80,
B.ZZL_C2 AS FD00353_Y_6_80,
B.BQ_C3 AS FD00354_Y_3_80,
A.C3 AS FD00355_Y_3_80,
B.ZZL_C3 AS FD00356_Y_6_80,
B.BQ_C4 AS FD00357_Y_3_80,
A.C4 AS FD00358_Y_3_80,
B.ZZL_C4 AS FD00359_Y_6_80FROM (
--各类型企业新增户数
SELECT T1.DM,NVL(SUM(T1.C1+T1.C5),0) AS C1,NVL(SUM(T1.C2),0) AS C2,NVL(SUM(T1.C3),0) AS C3,NVL(SUM(T1.C4),0) AS C4
FROM
(
SELECT
T.DM AS DM,
DECODE(T.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1,
DECODE(T.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2,
DECODE(T.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3,
DECODE(T.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4,
DECODE(T.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5
FROM
(
SELECT TAB.DM AS DM,TAB.CPCODE AS CPCODE,TAB.QYLX AS QYLX,TAB.CPTY AS CPTY
FROM
(
SELECT T1.DM AS DM,T1.CPCODE AS CPCODE,T1.QYLX AS QYLX,NVL(T2.CPCODE,0) AS CPTY
FROM
(
SELECT DISTINCT
SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM,
T.CPCODE AS CPCODE,
NSR.QYLX AS QYLX
FROM TS_JG_RDCP T,TS_CPCODE NSR
WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200901' AND T.SB_YM<='200906'
)T1 LEFT JOIN
(
SELECT DISTINCT
SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM,
T.CPCODE AS CPCODE,
NSR.QYLX AS QYLX
FROM TS_JG_RDCP T,TS_CPCODE NSR
WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200801' AND T.SB_YM<='200806'
)T2 ON T1.DM=T2.DM AND T1.CPCODE=T2.CPCODE AND T1.QYLX=T2.QYLX
)TAB WHERE TAB.CPTY=0
) T
GROUP BY T.DM,T.QYLX
)T1
GROUP BY T1.DM
)A,
(
--各类型企业总户数
SELECT T1.DM,
NVL(SUM(T1.C1+T1.C5),0) AS BQ_C1,
NVL(SUM(T1.C2),0) AS BQ_C2,
NVL(SUM(T1.C3),0) AS BQ_C3,
NVL(SUM(T1.C4),0) AS BQ_C4,
NVL(SUM(T2.C1+T2.C5),0) AS DBQ_C1,
NVL(SUM(T2.C2),0) AS DBQ_C2,
NVL(SUM(T2.C3),0) AS DBQ_C3,
NVL(SUM(T2.C4),0) AS DBQ_C4,
((SUM(T1.C1+T1.C5)-SUM(T2.C1+T2.C5))/SUM(T2.C1++T2.C5)) AS ZZL_C1,
((SUM(T1.C2)-SUM(T2.C2))/SUM(T2.C2)) AS ZZL_C2,
((SUM(T1.C3)-SUM(T2.C3))/SUM(T2.C3)) AS ZZL_C3,
((SUM(T1.C4)-SUM(T2.C4))/SUM(T2.C4)) AS ZZL_C4
FROM
(
SELECT
SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM,
DECODE(NSR.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1,
DECODE(NSR.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2,
DECODE(NSR.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3,
DECODE(NSR.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4,
DECODE(NSR.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5
FROM TS_JG_RDCP T,TS_CPCODE NSR
WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200901' AND T.SB_YM<='200906'
GROUP BY SUBSTR(NSR.NSR_SWJG_DM,0,7),NSR.QYLX
)T1,
(
SELECT
SUBSTR(NSR.NSR_SWJG_DM,0,7) AS DM,
DECODE(NSR.QYLX,'11', COUNT(DISTINCT T.CPCODE)) AS C1,
DECODE(NSR.QYLX,'21', COUNT(DISTINCT T.CPCODE)) AS C2,
DECODE(NSR.QYLX,'30', COUNT(DISTINCT T.CPCODE)) AS C3,
DECODE(NSR.QYLX,'90', COUNT(DISTINCT T.CPCODE)) AS C4,
DECODE(NSR.QYLX,'12', COUNT(DISTINCT T.CPCODE)) AS C5
FROM TS_JG_RDCP T,TS_CPCODE NSR
WHERE T.CPCODE=NSR.CPCODE AND T.SB_YM>='200801' AND T.SB_YM<='200806'
GROUP BY SUBSTR(NSR.NSR_SWJG_DM,0,7),NSR.QYLX
)T2 where T1.dm=T2.dm group by T1.dm)B LEFT JOIN T01_SWJG SW
ON B.DM=SUBSTR(SW.SWJG_DM,0,7) AND LAYER='2' WHERE A.DM=B.DM
因各种查询的灵活性 现在的数据量已经是 最小的整合了查询条件 全都是自定义的
正常都是按年统计 就是1-12了。索引这些都经过优化了
包括 数据也分类整合优化了现在的速度在10左右所以最后想 看看在SQL语法 和写法上 还能不能找到 更有效的 写法
(nvl(sum_2009, 0) - nvl(sum_2008, 0)) / sum_2008 AS 增长率,
sum_2009 / sum_total AS 占比
from (select code,
sum(case
when DATE >= '200901' AND DATE <= '200912' then
amnt
end) sum_2009,
sum(case
when DATE >= '200801' AND DATE <= '200812' then
amnt
end) sum_2008,
sum(amnt) sum_total
FROM TAB
WHERE DATE >= '200801'
AND DATE <= '200912'
GROUP BY CODE)
我可以这样写SELECT ID,SUM(RMB) FROM TAB
GROUP BY ID WITH CUBE得出来的值最后是有个汇总合计的但是在PSQL里面 这样的写法 就是不支持的那请问达人
在PSQL里 代替WITH CUBE 这个函数的函数是什么
oracle新建QQ群:54775466
欢迎爱好者 一起共同探讨
本群欢迎您的到来