我正在尝试将table1
table2
中的一些内容合并到另一个table3
中。我在PostgreSQL
中启动并运行了以下查询:
CREATE TABLE table3 AS SELECT
table1.orig_zon AS orig_zon,
table1.dest_zon AS dest_zon,
table1.same_zon AS same_zon,
table1.adjacent AS adjacent,
table1.distance AS distance,
table1.da_ivtt AS da_ivtt1,
table1.da_ovtt AS da_ovtt1,
table1.tr_avail AS tr_avail1,
table1.tr_ivtt AS tr_ivtt1,
table1.tr_ovtt AS tr_ovtt1,
table1.tr_cost AS tr_cost1,
table1.au_cost AS au_cost1,
table1.sr_ivtt AS sr_ivtt1,
table1.sr_cost AS sr_cost1,
table2.da_ivtt AS da_ivtt2,
table2.da_ovtt AS da_ovtt2,
table2.tr_avail AS tr_avail2,
table2.tr_ivtt AS tr_ivtt2,
table2.tr_ovtt AS tr_ovtt2,
table2.tr_cost AS tr_cost2,
table2.au_cost AS au_cost2,
table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2,
FROM table1, table2
WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));
我已经测试了一个非常小的虚拟数据集的查询,结果符合预期。但是,我的实际数据库是SQLite
的,当我通过 Linux shell 在 sqlite3 中运行它时,我收到错误如下:
Error: near "table1": syntax error
谁能指出我需要做的修改才能在SQLite中工作?感谢您的帮助!
编辑1:删除尾随逗号后(请参阅下面的克雷格评论),问题仍然存在。
编辑2:玩了一会儿后,我找到了解决方法。看起来很愚蠢,但如果我要在一行中传递查询的内容,该语句就可以了!
table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2 FROM table1, table2 WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));
哦!类型转换的祸根!!
SQL 不允许在SELECT
列表中使用尾随逗号。
table2.sr_cost AS sr_cost2,
FROM table1, table2
应该是:
table2.sr_cost AS sr_cost2
FROM table1, table2
您需要删除多余的逗号
table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2
FROM table1, table2