我想在以 CREATE VIEW 开头并以“;”结尾的段落中搜索模式“FROM”并将结果保存在 csv 文件中。例如,如果我有以下文件:
CREATE VIEW view1
AS something
FROM table1 ,table2 as A, table3 (something FROM table4)
FROM table5, table6
USING file1
;
CREATE VIEW view2
FROM table1 ,table2 ,table6 ,table4
something
something
FROM table5 ,table7 (something FROM table4 ,table5(this is something FROM table8)
USING file2
;
我希望得到以下结果:
view1;table1
view1;table2
view1;table3
view1;table4
view1;table5
view1;table6
view2;table1
view2;table2
view2;table6
view2;table4
view2;table5
view2;table7
view2;table4
view2;table5
view2;table8
答案1
编辑:忘记了第一行(所以我将 L++ 更改为 ++L):Edit2:修复了正则表达式,使其在最后一个 FROM 之前不会“通配”整个括号
我们也许能够“创造性地”使用字段分隔来删除我们不想保留的内容并只检索表名:
$ LC_ALL="C" awk -v csvsep=';' -v separators='FROM *| *, *| +as[^,]*| *[(][^()]*FROM *| *[)] *' '
/CREATE VIEW/ { name=$NF }
/FROM / { nb=split($0,tables,separators);
for(i=1;i<=nb;i++) {
(tables[i]~/[A-Za-z]/) ? line[++L]=name csvsep tables[i] : rem="Otherwise nothing to add" }
}
END { for(i=1;i<=L;i++) { print line[i] } }'
然后我们喂它:
CREATE VIEW view1
AS something
FROM table1 ,table2 as A, table3 (something FROM table4)
FROM table5, table6
USING file1
;
CREATE VIEW view2
FROM table1 ,table2 ,table6 ,table4
something
something
FROM table5 ,table7 (something FROM table4 ,table5(this is something FROM table8)
USING file2
;
它给出了预期的结果:
view1;table1
view1;table2
view1;table3
view1;table4
view1;table5
view1;table6
view2;table1
view2;table2
view2;table6
view2;table4
view2;table5
view2;table7
view2;table4
view2;table5
view2;table8
注意:我们只处理包含“FROM”的行,因此如果您有创意的 FROM 行(在多行上......),如果没有更多的魔法,它就无法工作。
答案2
TXR解决方案:
@(define word (w))@{w /[^,\s()]+/}@(end)
@(collect)
@ (cases)
CREATE VIEW @view@/ /
@ (or)
CREATE VIEW @view
@ (end)
@ (collect)
@ (coll :vars (table))@\
FROM @(word first-tbl)@\
@(coll :vars (rest-tbl) :gap 0)@\
@/\s*,\s*/@(word rest-tbl)@\
@(maybe) as @(word something)@(end)@\
@(end)@\
@(merge table first-tbl rest-tbl)@\
@(end)
@ (until)
CREATE@(skip)
@ (end)
@ (flatten table)
@(end)
@(output)
@ (repeat)
@ (repeat)
@view;@table
@ (end)
@ (end)
@(end)
跑步:
$ txr 提取.txr 数据 视图1;表1 视图1;表2 视图1;表3 视图1;表4 视图1;表5 视图1;表6 视图2;表1 视图2;表2 视图2;表6 视图2;表4 视图2;表5 视图2;表7 视图2;表4 视图2;表5 视图2;表8
请注意,如果as
出现为AS
,则必须将其写入代码中;例如使用@/[Aa][Ss]/
.