开源工具：大量SQL代码语法转换实战经验：PIVOT函数重写（案例2）

算法解析 2026-09-10 17:35:08 3

最近在搞数据库迁移项目，甲方要把Oracle数据库里的代码往其他数据库上迁。Oracle有个PIVOT函数用得还挺爽的，但目标数据库不支持这语法，遇到一堆代码要改，手工一个个翻不仅累还容易出错。踩了几次坑之后，试了试ZGLanguage这个开源工具，发现批量处理还挺香的，这篇把实战经验分享出来。

先说背景：跨数据库迁移时，SQL语法不兼容是常事。碰到大量代码需要重写的情况，手工处理费时费力还容易出bug，能不能用工具实现批量自动转换？答案是能，ZGLanguage就是干这个的。

拿PIVOT函数开刀

假设原始SQL长这样：

SELECT *
FROM table2222 PIVOT ( SUM(sales) AS ss1, SUM(cogs) AS sc FOR (yr, qtr) IN ( (2001, 'Q1'), (2001, 'Q2'), (2001, 'Q3'), (2001, 'Q4') )
) tmp
;

通过ZGLanguage的转换规则跑一遍，出来的是这样的结果：

SELECT *
FROM
( select ###,###,### SUM(case when yr=2001 and qtr='Q1' then sales else null end ) AS "2001_Q1_ss1", SUM(case when yr=2001 and qtr='Q2' then sales else null end ) AS "2001_Q2_ss1", SUM(case when yr=2001 and qtr='Q3' then sales else null end ) AS "2001_Q3_ss1", SUM(case when yr=2001 and qtr='Q4' then sales else null end ) AS "2001_Q4_ss1", SUM(case when yr=2001 and qtr='Q1' then cogs else null end ) AS "2001_Q1_sc", SUM(case when yr=2001 and qtr='Q2' then cogs else null end ) AS "2001_Q2_sc", SUM(case when yr=2001 and qtr='Q3' then cogs else null end ) AS "2001_Q3_sc", SUM(case when yr=2001 and qtr='Q4' then cogs else null end ) AS "2001_Q4_sc" from table2222 where (yr, qtr) IN ( (2001, 'Q1') , (2001, 'Q2') , (2001, 'Q3') , (2001, 'Q4') ) group by ###,###,### ) tmp
;

PIVOT的本质就是行转列，这里被拆成了多个CASE WHEN加SUM的组合，字段别名也自动拼出来了。

转换规则长啥样

规则的syn文件内容比较长，这里只贴关键部分：

__DEF_FUZZY__ Y
__DEF_DEBUG__ N
__DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ __DEF_PATH__ __FROM_PIVOT_2_1__
1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @ + __SUB_SELECT__ : pvt @ | pivot : x1 @ | (
N : fun @ | __NAME__ __//__ sum .... : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ %__IF_KW__ CAN_SKIP | as : colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for : y1 @ | __COLS_4_FOR__ : in2 @ | in : y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | ) : x2 @ | )
------------------------------------------------------------------
1 : frm @ | from : tab @ | __TABLE_NAME__ : ssl @ | __SUB_SELECT__ : pvt @ | pivot : x1 @ | (
N : fun @ | __NAME__ : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ | as : colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ | for : y1 @ | __COLS_4_FOR__ : in2 @ | in : y5 @ | (
N : y3 @ | __\b__ : y1 @ | __COLS_4_FOR__ : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
1 : for2 @ | where : y1 @ | __COLS_4_FOR__ : in2 @ | in : y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | ) : x2 @ | ) __DEF_PATH__ __FROM_PIVOT_2_2__
1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @ + __SUB_SELECT__ : pvt @ | pivot : x1 @ | (
N : fun @ | __NAME__ : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ %__IF_KW__ CAN_SKIP | as : colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for : y1 @ | __COLS_4_FOR__ : in2 @ | in : y5 @ | (
N : y3 @ | __COLS_VALUES__
e : dh7 @ | ,
1 : y6 @ | )
1 : where @ | where : y11 @ | __COLS_4_FOR__ : in21 @ | in : y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | ) : x2 @ | )
------------------------------------------------------------------
1 : frm @ | from : tab @ | __TABLE_NAME__ : ssl @ | __SUB_SELECT__ : pvt @ | pivot : x1 @ | (
N : fun @ | __NAME__ : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ | as : colas @ | __NAME__
* : y3 @ | __COLS_VALUES__
e : y3 @ | ,
1 : where @ | where : y11 @ | __COLS_4_FOR__ : in21 @ | in : y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | ) : x2 @ | ) __DEF_PATH__ __FROM_PIVOT_2_3__
1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @ + __SUB_SELECT__ : pvt @ | pivot : x1 @ | (
N : fun @ | __NAME__ : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ %__IF_KW__ CAN_SKIP | as : colas @ | __NAME__ : cw @ | __CASE_WHEN__ : as2 @ | as : y2 @ | __VALUE_2_COL__
e : y3 @ | ,
1 : where @ | where : y11 @ | __COLS_4_FOR__ : in21 @ | in : y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | ) : x2 @ | )
--------------------------------------------------------------
1 : frm @ | from : x1 @ | ( : x1 @ STRING | select ###,###,###
N : fun @ | __NAME__ : fs @ | ( : cw @ | __CASE_WHEN__ : col1 @ | __NAME__ : col1 @ STRING | else null end : fe @ | ) : as1 @ | as : y2 @ | __VALUE_2_COL__ : colas @ \ __NAME__ : colas @ \ "
e : y3 @ | ,
1 : pvt @ | from : tab @ | __TABLE_NAME__ : ssl @ | __SUB_SELECT__
1 : where @ | where : y11 @ | __COLS_4_FOR__ : in21 @ | in : y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | ) : x1 @ STRING | group by ###,###,### : x2 @ | ) __DEF_SUB_PATH__ __VALUE_2_COL__
N : x1 @ | __INT__
+ : x2 @ | ' : x3 @ | __ANY__ : x4 @ | '
------------------------------------------------------------------
1 : x1 @ | " : x3 @ | "
N : x1 @ \ __INT__ : x3 @ \ __ANY__ : x1 @ \ _ : x3 @ \ _ __DEF_SUB_PATH__ __CASE_WHEN__
N : x1 @ | __NAME__ : x2 @ | = : x3 @ | __INT__ : x4 @ + __STRING__
e : x5 @ | and
------------------------------------------------------------------
1 : x1 @ STRING | case when
N : x1 @ | __NAME__ : x2 @ | = : x3 @ | __INT__ : x4 @ | __STRING__
e : x5 @ | and
1 : x1 @ | then __DEF_SUB_PATH__ __COLS_VALUES__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | ) : y1 @ | (
N : y2 @ | __INT__ : y3 @ + __STRING__
e : y4 @ | ,
1 : y5 @ | )
----------------------------------------------------------------------
N : x2 @ | __NAME__ : x2 @ / = : y2 @ / __INT__ : y3 @ / __STRING__
e : x2 @ | and
1 : x2 @ | as N : y2 @ | __INT__ : y3 @ | __STRING__ __DEF_SUB_PATH__ __COLS_4_FOR__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | ) __DEF_SUB_PATH__ __VALUE_4_IN__
1 : x1 @ | (
N : x2 @ | __INT__ : x3 @ + __STRING__
e : x4 @ | ,
1 : x5 @ | ) __DEF_SUB_PATH__ __TABLE_NAME__
1 : srctab @ | __NAME__
+ : schema @ | __NAME__ : pp @ | . : srctab2 @ | __NAME__ __DEF_SUB_PATH__ __SUB_SELECT__
1 : x1 @ | __SUB__ __DEF_PATH__ __SUB__
1 : x1 @ | ( N : x2 @ | __ALL_STR__ : x3 @ + __SUB__
1 : x4 @ | ) __DEF_STR__ __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/ __DEF_STR__ __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view __DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789 __DEF_STR__ __INT__
<1,100>
[1,100]0123456789 __DEF_SUB_PATH__ __STRING__
1 : x1 | ' : x2 | __ANY__ : x3 | '

分步转换的完整流程

PIVOT函数的转换规则比较复杂，一次跑不完，得分三步走：

ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl

第一轮转换，触发FROM_PIVOT_2_1规则，主要做两件事：一是把FOR后面的列和IN里的枚举值做一对一映射；二是把FOR结构转成WHERE子句。跑完之后长这样：

SELECT *
FROM table2222 PIVOT
( SUM ( sales ) AS ss1 , SUM ( cogs ) AS sc FOR (yr, qtr) IN ( (yr, qtr) (2001, 'Q1') , (yr, qtr) (2001, 'Q2') , (yr, qtr) (2001, 'Q3') , (yr, qtr) (2001, 'Q4') ) where (yr, qtr) IN ( (2001, 'Q1') , (2001, 'Q2') , (2001, 'Q3') , (2001, 'Q4') )
) tmp
;

第二轮转换，触发FROM_PIVOT_2_2规则，对SUM列和qtr列做笛卡尔积，同时把枚举值提取出来准备生成新字段别名。结果：

SELECT *
FROM table2222 PIVOT
( SUM(sales) AS ss1 yr = 2001 and qtr = 'Q1' as 2001 'Q1' , SUM(sales) AS ss1 yr = 2001 and qtr = 'Q2' as 2001 'Q2' , SUM(sales) AS ss1 yr = 2001 and qtr = 'Q3' as 2001 'Q3' , SUM(sales) AS ss1 yr = 2001 and qtr =