异常代码:

20:17:49.331 [http-nio-8081-exec-6] ERROR c.a.d.f.s.StatFilter - [mergeSql,169] - merge sql error, dbType oracle, druid-1.2.8, sql : WITH
        orgn AS(SELECT w.dwid,w.qk,w.qkdm,w.jhid FROM cd_orgn_source_attr w WHERE w.dwid='xAJLaafdjei321jlaj13nanfasX000113'),
        pclog AS(SELECT p.orgn_id,p.sand_layer_group_name AS cw ,p.soak_volume,p.thickness,p.soak_thickness,p.perforate_orgn_top_depth top FROM pc_log_desc_infuse p WHERE p.soak_volume>0 AND p.sand_layer_group_name IS NOT NULL ORDER BY top ASC),
res AS(SELECT *FROM orgn w INNER JOIN pclog p ON w.jhid=p.orgn_id),
cwres AS (SELECT cw,orgn_id FROM res GROUP BY orgn_id,cw ORDER BY cw),
layerdata AS(SELECT distinct(cw) AS "cw",SUM(thickness) AS "sy",SUM(soak_thickness) AS "yx",(SELECT COUNT(c.orgn_id) FROM cwres c WHERE c.cw=r.cw ) AS "cs" FROM res r WHERE r.QKDM=? GROUP BY cw),
        sumdata AS(SELECT SUM("cs") AS "cs_sum",SUM("sy") AS "sy_sum",SUM("yx") AS "yx_sum" FROM layerdata),
res1 AS(SELECT l.*,s.*,(l."sy"/NULLIF(s."sy_sum",0)*100) AS "p_sy",(l."yx"/NULLIF(s."yx_sum",0)*100) AS "p_yx",(l."cs"/NULLIF(s."cs_sum",0)*100)AS "p_cs" FROM layerdata l,sumdata s),
res2 AS(SELECT "cw",k AS "key",CASE
            when v >= 0 and v < 35 then 'c1'
            when v >= 35 and v < 40 then 'c2'
            when v >= 40 and v < 50 then 'c3'
            when v >= 50 and v < 75 then 'c4'
            when v >= 75 and v <= 100 then 'c5'
            END AS "value"
            FROM res1 t UNPIVOT (v  FOR  k IN("p_cs","p_sy","p_yx")) t),
            res3 AS (SELECT *FROM (select t."key",t."value",count(0) AS cc from res2 t group BY t."key",t."value") PIVOT (SUM(cc) for "value" in ('c1' as "c1",'c2' as "c2",'c3' as "c3",'c4' as "c4",'c5' as "c5")))
            select * from res3
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'      FROM res1 t UNPIVOT (v  FOR  k IN("p_cs","', expect ), actual null, pos 1371, line 18, column 26, token IDENTIFIER UNPIVOT

问题描述:

1.前端可以正常返回结果，但是后端控制台报错 错误代码:

2.输出的sql可以在sqlplus中正常执行

20:17:49.331 [http-nio-8081-exec-6] ERROR c.a.d.f.s.StatFilter - [mergeSql,169] - merge sql error, dbType oracle, druid-1.2.8, sql

问题分析及解决:

        开始怀疑是不是使用了Oracle函数 PIVOT 和 UNPIVOT 导致的问题，转念一想阿里天天都在用 pivot 和 unpivot 这是不可能的，然后开始度娘......

方法1.（升级druid版本）(失败)

        查询相关资料有人说是  druid版本过低(1.2.5),然后果断 升级成  druid-1.2.8....（然并卵）.

方法2.(修改application.yaml)中druid配置 （成功）

        修改

       spring:datasource:druid:filter:merge-sql：false

        

问题解决了，确实不报错了，可是这和掩耳盗铃有什么区别，简直是侮辱码畜的职业素养。

方法3.（勇敢面对问题）

        回头仔细看下异常代码，

com.alibaba.druid.sql.parser.ParserException: syntax error, error in :
'      FROM res1 t UNPIVOT (v  FOR  k IN("p_cs","', expect ), actual null, pos 1371, line 18, column 26, token IDENTIFIER UNPIVOT
	at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:841)
	at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:849
.......

 还是得从SQL下手，

 问题出现在红框的SQL语法问题，虽然在sqldevlop中可以正常运行但是还是不够严谨，修改成绿框中内容后，系统就正常了。

总结：

merge sql error 不是真正的ERROR，而是告诉你,骚年! 你的SQL水平有待提高......