源代码：使用开源代码解析器对SQL代码进行拆解标注
开源代码解析器对SQL代码进行解析
·
# 假设存在 SQL 代码如下所示：
DROP TABLE IF EXISTS TMP_IPT.TMP_XXX_01
;
CREATE TABLE IF NOT EXISTS TMP_IPT.TMP_XXX_01
AS
with tmp1 as
(
select * from table1
UNION
select * from table2
)
, tmp2 as
(
select 'asdf' + "x" from table9
)
, tmp3 as
(
select * from table10
UNION
select * from table11
)
select *
from (select *
from (select * from table3) t1
, (select *
from (select * from table4)) t2
) t1
join table5
on 1=1
join (select * from table6
union all
select * from table7
union all
select * from table8
) t2
on 1=1
union all ---------------
select *
from (with tmp_s as(
select * from table13
)
select *
from table12 t1
, tmp_s t2
where t1.a = t2.b
) T1
;
# 通过 ZGLanguage 配置拆解标注规则如下所示：
__DEF_FUZZY__ N
__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__ __DROP_TABLE__
1 : cre @ | drop
: tab @ | table
: if @ CAN_SKIP | if
: exists @ CAN_SKIP | exists
1 : tartab @ | __NAME__
+ : schema @ | __NAME__
: pp @ | .
: tartab2 @ | __NAME__
1 : end @ | ;
-------------------------------------------------------------------
1 : cre @ | drop
: tab @ | table
: if @ | if
: exists @ | exists
: tartab @ | __NAME__
: schema @ | __NAME__
: pp @ \ .
: tartab2 @ \ __NAME__
: end @ | {;;;}
__DEF_PATH__ __CREATE_TABLE_SELECT__
11 : cre @ | create
: tab @ | table
: if @ CAN_SKIP | if
: not @ CAN_SKIP | not
: exists @ CAN_SKIP | exists
11 : tartab @ | __NAME__
++ : schema @ | __NAME__
: pp @ | .
: tartab2 @ | __NAME__
11 : as @ | as
: with @ CAN_SKIP | __WITH_AS_SELECT__
: sel @ | __SELECT__
0N : uni @ | __UNION_SELECT__
1 : end @ | ;
-------------------------------------------------------------------
1 : cre @ | create
: tab @ | table
: if @ | if
: not @ | not
: exists @ | exists
: tartab @ | {###}<tartab>{###}
: tartab @ \ __NAME__
: tartab @ \ {###}</tartab>{###}
: schema @ | {###}<schema>{###}
: schema @ \ __NAME__
: schema @ \ {###}</schema>{###}
: pp @ \ .
: tartab2 @ \ {###}<tartab>{###}
: tartab2 @ \ __NAME__
: tartab2 @ \ {###}</tartab>{###}
: as @ | as
: with @ | __WITH_AS_SELECT__
: sel @ | __SELECT__
: end @ | ;
-------------------------------------------------------------------
1 : cre @ | create
: tab @ | table
: if @ | if
: not @ | not
: exists @ | exists
: tartab @ | {###}<tartab>{###}
: tartab @ \ __NAME__
: tartab @ \ {###}</tartab>{###}
: schema @ | {###}<schema>{###}
: schema @ \ __NAME__
: schema @ \ {###}</schema>{###}
: pp @ \ .
: tartab2 @ \ {###}<tartab>{###}
: tartab2 @ \ __NAME__
: tartab2 @ \ {###}</tartab>{###}
: as @ | as
: with @ | __WITH_AS_SELECT__
: sel @ | {###}<union>{###}
: sel @ | __SELECT__
: sel @ | {###}</union>{###}
N : uni @ | __UNION_SELECT__
1 : end @ | {;;;}
__//__ ==================================================================
__DEF_SUB_PATH__ __WITH_AS_SELECT__
1 : x1 @ | __FIRST_WITH_AS_SELECT__
N : x2 @ CAN_SKIP | __NEXT_WITH_AS_SELECT__
__DEF_SUB_PATH__ __FIRST_WITH_AS_SELECT__
11 : x1 @ | with
: name @ | __NAME__
: x3 @ | as
: x4 @ | (
: x5 @ | __SELECT__
0N : x6 @ | __UNION_SELECT__
1 : with_end @ | )
------------------------------------------------------------------------
1 : x1 @ | {###}<with>{###}
: name @ STRING | drop table if exists
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: name @ | ;
: name @ STRING | create table
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: x3 @ | as
: x5 @ | __SELECT__
: with_end @ | {###}</with>{###}
------------------------------------------------------------------------
1 : x1 @ | {###}<with>{###}
: name @ STRING | drop table if exists
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: name @ | ;
: name @ STRING | create table
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: x3 @ | as
: x5 @ | {###}<union>{###}
: x5 @ | __SELECT__
: x5 @ | {###}</union>{###}
N : x6 @ | __UNION_SELECT__
1 : with_end @ | {###}</with>{###}
__DEF_SUB_PATH__ __NEXT_WITH_AS_SELECT__
11 : x1 @ | ,
: name @ | __NAME__
: x3 @ | as
: x4 @ | (
: x5 @ | __SELECT__
0N : x6 @ | __UNION_SELECT__
1 : with_end @ | )
-------------------------------------------------------------------------
1 : x1 @ | {###}<with>{###}
: name @ STRING | drop table if exists
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: name @ | ;
: name @ STRING | create table
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: x3 @ | as
: x5 @ | __SELECT__
: with_end @ | {###}</with>{###}
-------------------------------------------------------------------------
1 : x1 @ | {###}<with>{###}
: name @ STRING | drop table if exists
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: name @ | ;
: name @ STRING | create table
: name @ | {###}<withtab>{###}
: name @ \ __NAME__
: name @ \ {###}</withtab>{###}
: x3 @ | as
: x5 @ | {###}<union>{###}
: x5 @ | __SELECT__
: x5 @ | {###}</union>{###}
N : x6 @ | __UNION_SELECT__
1 : with_end @ | {###}</with>{###}
__DEF_SUB_PATH__ __UNION_SELECT__
1 : union @ %__IF_KW__ | union
: x2 @ CAN_SKIP | all
: x3 @ | __SELECT__
---------------------------------------------------------------------------
1 : union @ | union
: x2 @ | all
: x3 @ | {###}<union>{###}
: x3 @ | __SELECT__
: x3 @ | {###}</union>{###}
__DEF_SUB_PATH__ __SUB_SELECT__
11 : x1 @ | (
: with @ CAN_SKIP | __WITH_AS_SELECT__
: x2 @ | __SELECT__
0N : xU @ | __UNION_SELECT__
1 : x3 @ | )
: x4 @ CAN_SKIP | as
: son @ CAN_SKIP | __NAME__
----------------------------------------------------
1 : x1 @ | {###}<subsel>{###}
: with @ | __WITH_AS_SELECT__
: x2 @ | __SELECT__
: x3 @ | {###}</subsel>{###}
: x4 @ | as
: son @ | __NAME__
----------------------------------------------------
1 : x1 @ | {###}<subsel>{###}
: with @ | __WITH_AS_SELECT__
: x2 @ | {###}<union>{###}
: x2 @ | __SELECT__
: x2 @ | {###}</union>{###}
N : xU @ | __UNION_SELECT__
1 : x3 @ | {###}</subsel>{###}
: x4 @ | as
: son @ | __NAME__
__DEF_SUB_PATH__ __VALUE_SELECT__
1 : x1 @ | (
: x2 @ | __SELECT__
: x3 @ | )
__DEF_SUB_PATH__ __SELECT__
1 : x1 @ | select
: x2 @ CAN_SKIP | distinct
N : x3 @ | __PATH_4_EXPR__
: as @ CAN_SKIP | as
: colname @ CAN_SKIP | __NAME__
e : colspl @ | ,
1 : x7 @ | from
: x8 @ | __TABLE_NAME__
: x9 @ + __SUB_SELECT__
: x4 @ CAN_SKIP | as
: ssn @ CAN_SKIP | __NAME__
N : xa @ CAN_SKIP | __JOIN_TABLE__
1 : xb @ CAN_SKIP | __WHERE__
: xc @ CAN_SKIP | __GROUP_BY__
: xd @ CAN_SKIP | __ORDER_BY__
: xe @ CAN_SKIP | __DISTRIBUTED_BY__
__DEF_SUB_PATH__ __TABLE_NAME__
1 : srctab @ | __NAME__
+ : schema @ | __NAME__
: pp @ | .
: srctab_2 @ | __NAME__
1 : as @ CAN_SKIP | as
: srctabas @ CAN_SKIP | __NAME__
__DEF_SUB_PATH__ __JOIN_TABLE__
11 : x0 @ | join
++ : x1 @ | inner
: x2 @ | join
++ : x5 @ | left
: x6 @ CAN_SKIP | out
: x7 @ | join
++ : x12 @ | right
: x13 @ CAN_SKIP | out
: x14 @ | join
++ : x17 @ | full
: x18 @ CAN_SKIP | out
: x19 @ | join
++ : x20 @ | ,
11 : x21 @ | __TABLE_NAME__
: x2s @ + __SUB_SELECT__
0 : x22 @ | on
0 : x23 @ | __PATH_4_EXPR__
__DEF_SUB_PATH__ __WHERE__
1 : x1 @ | where
N : x2 @ | __PATH_4_EXPR__
: x3 @ + __EXISTS_SELECT__
__DEF_SUB_PATH__ __GROUP_BY__
11 : x1 @ | group
: x2 @ | by
NN : x3 @ | __PATH_4_EXPR__
ee : x4 @ | ,
01 : x5 @ | having
0 : x6 @ | __PATH_4_EXPR__
__DEF_SUB_PATH__ __ORDER_BY__
1 : x1 @ | order
: x2 @ | by
N : x3 @ | __PATH_4_EXPR__
: x4 @ CAN_SKIP | desc
: x5 @ + asc
e : x6 @ | ,
__DEF_SUB_PATH__ __PARTITION_BY__
1 : x11 | partition
: x12 | by
N : x13 | __PATH_4_EXPR__
e : x16 | ,
__DEF_SUB_PATH__ __DISTRIBUTED_BY__
1 : x11 | distributed
: x12 | by
: n12 | (
N : x13 | __PATH_4_EXPR__
e : x16 | ,
1 : x17 | )
__DEF_SUB_PATH__ __EXISTS_SELECT__
01 : x1 | not
1 : x2 | exists
: x3 | (
: x4 | __SELECT__
: x5 | )
__DEF_SUB_PATH__ __SUB_PATH_4_EXPR__
1 : x1 | (
N : x2 | __PATH_4_EXPR__
e : x3 | ,
1 : x4 | )
__DEF_SUB_PATH__ __PATH_4_EXPR__
N : x1 @ | __NAME__
: x2 @ + __INT__
: x3 @ + __FLOAT__
: x4 @ + __CASE_WHEN__
: x5 @ + __STRING__
: x51 @ + __STRING_NAME__
: x6 @ + __CAST_AS__
: x8 @ + __SUB_PATH_4_EXPR__
: VS @ + __VALUE_SELECT__
: xa @ + __FUNCTION__
: xon @ + __OTH_NAME__
: x7 @ + __OTH_FUN__
: x9 @ + =
: x10 @ + <>
: x11 @ + !=
: x12 @ + >
: x13 @ + >=
: x14 @ + <
: x15 @ + <=
: x18 @ + +
: x19 @ + -
: x21 @ + *
: x22 @ + /
: x23 @ + ||
: x25 @ %__IF_KW__ + between
: x26 @ %__IF_KW__ + and
: x27 @ %__IF_KW__ + or
: x28 @ %__IF_KW__ + like
: x29 @ %__IF_KW__ + in
: x30 @ %__IF_KW__ + is
: x31 @ %__IF_KW__ + not
: x32 @ %__IF_KW__ + null
__DEF_SUB_PATH__ __OTH_NAME__
1 : other_name @ | __NAME__
: pp @ | .
: column @ | __NAME__
: all_col @ + *
__DEF_SUB_PATH__ __OTH_FUN__
N : schema | __NAME__
: pp | .
1 : fun | __FUNCTION__
__DEF_SUB_PATH__ __FUNCTION__
11 : 函数名 @ | __NAME__
: x2 @ | (
: y2 @ CAN_SKIP | distinct
NN : x3 @ CAN_SKIP | __PATH_4_EXPR__
ee : x4 @ CAN_SKIP | ,
11 : x5 @ | )
0 : v1 @ | over
0 : v2 @ | (
0 : v3 @ CAN_SKIP | __PARTITION_BY__
0 : v4 @ CAN_SKIP | __ORDER_BY__
0 : v5 @ | )
__DEF_SUB_PATH__ __CAST_AS__
1 : x1 @ | cast
: x2 @ | (
1 : x3 @ | __PATH_4_EXPR__
: x4 @ | as
: x5 @ | date
: x6 @ + int
: n1 @ + double
: n2 @ + float
: n3 @ + bigint
: x7 @ + __X_CHAR__
: x8 @ + __DECIMAL__
1 : xx @ | )
__DEF_SUB_PATH__ __CASE_WHEN__
1 : x1 @ | case
N : x2 @ | when
: x3 @ | __PATH_4_EXPR__
: x4 @ | then
: x5 @ | __PATH_4_EXPR__
1 : x6 @ CAN_SKIP | else
: x7 @ CAN_SKIP | __PATH_4_EXPR__
: x8 @ | end
__DEF_SUB_PATH__ __DECIMAL__
111 : x1 | decimal
0 : x2 | (
01 : x3 | __INT__
00 : x4 | ,
00 : x5 | __INT__
01 : x6 | )
__DEF_SUB_PATH__ __X_CHAR__
11 : x1 | varchar
: n1 + nvarchar
: n2 + char
: n3 + varchar2
: n4 + nvarchar2
0 : x2 | (
0 : x3 | __INT__
0 : x6 | )
__DEF_SUB_PATH__ __VAR_NAME__
1 : x1 | $
: x2 | {
: x3 | __NAME__
: x4 | }
__DEF_STR__ __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] select inner left join on from where group order partition by having union all with as table set between and or like in is not null
__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 | '
__DEF_SUB_PATH__ __STRING_NAME__
1 : x1 | "
: x2 | __ANY__
: x3 | "
+ : x4 | `
: x5 | __ANY__
: x6 | `
# 执行后得到结果SQL如下所示：
DROP TABLE IF EXISTS TMP_IPT.TMP_XXX_01
{;;;}
CREATE TABLE IF NOT EXISTS {###}<schema>{###}TMP_IPT{###}</schema>{###}.{###}<tartab>{###}TMP_XXX_01{###}</tartab>{###}
AS
{###}<with>{###} drop table if exists {###}<withtab>{###}tmp1{###}</withtab>{###} ; create table {###}<withtab>{###}tmp1{###}</withtab>{###} as {###}<union>{###}
select * from table1 {###}</union>{###}
UNION {###}<union>{###}
select * from table2 {###}</union>{###}
{###}</with>{###}
{###}<with>{###} drop table if exists {###}<withtab>{###}tmp2{###}</withtab>{###} ; create table {###}<withtab>{###}tmp2{###}</withtab>{###} as
select 'asdf' + "x" from table9 {###}</with>{###}
{###}<with>{###} drop table if exists {###}<withtab>{###}tmp3{###}</withtab>{###} ; create table {###}<withtab>{###}tmp3{###}</withtab>{###} as {###}<union>{###}
select * from table10 {###}</union>{###}
UNION {###}<union>{###}
select * from table11 {###}</union>{###}
{###}</with>{###} {###}<union>{###}
select *
from {###}<subsel>{###} select *
from {###}<subsel>{###} select * from table3 {###}</subsel>{###} t1
, {###}<subsel>{###} select *
from {###}<subsel>{###} select * from table4 {###}</subsel>{###} {###}</subsel>{###} t2
{###}</subsel>{###} t1
join table5
on 1=1
join {###}<subsel>{###} {###}<union>{###}select * from table6 {###}</union>{###}
union all {###}<union>{###}
select * from table7 {###}</union>{###}
union all {###}<union>{###}
select * from table8 {###}</union>{###}
{###}</subsel>{###} t2
on 1=1 {###}</union>{###}
union all --------------- {###}<union>{###}
select *
from {###}<subsel>{###} {###}<with>{###} drop table if exists {###}<withtab>{###}tmp_s{###}</withtab>{###} ; create table {###}<withtab>{###}tmp_s{###}</withtab>{###} as
select * from table13 {###}</with>{###}
select *
from table12 t1
, tmp_s t2
where t1.a = t2.b {###}</subsel>{###}
T1 {###}</union>{###}
{;;;}源代码下载：http://kexuejisuan.com/
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
5
0- 0
已为社区贡献5条内容
所有评论(0)