加入两个表的列
问题描述:
所以我有两张桌子,说comment_table和post_table,加入两个表的列
comment_table:
link_id body
t3_100 people on * are smart
t3_100 * is a good place to raise questions
t3_101 where can I learn sql
t3_102 Happy Eastern
t3_102 where did my bunny go?
post_table
id title
100 Thought on *
101 sql beginner
102 Eastern
105 Title that has no comments
“link_id” 是由串联 't3_' +来自post_table的ID。我想要的是两个“id”加入这两个表。
期望输出
id title link_id body
100 Thought on * t3_100 people on * are smart
100 Thought on * t3_100 * is a good place to raise questions
101 sql beginner t3_101 where can I learn sql
102 Eastern t3_102 Happy Eastern
102 Eastern t3_102 where did my bunny go?
105 Title that has no comments t3_105 NULL
这里是脚本我有,
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table as PT
LEFT OUTER JOIN comment_table as CT
ON PT.ID = CT.concat('t3_', link_id)
它语法错误,你怎么认为我可以解决这个问题,以获得预期的输出?
答
您正在为函数指定别名,它应该与字段。
这会工作。
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table as PT
LEFT OUTER JOIN comment_table as CT
ON CT.link_id = CONCAT('t3_', PT.ID)
答
你没有正确使用concat
。所以你想连接“t3_”与id
从post_table
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table as PT
LEFT OUTER JOIN comment_table as CT
ON CT.link_id = concat('t3_', PT.id)
在comment_table
的link_id
是一个与“t3_”:试试这个。
另外,要加入这样的表是非常可怕的。这将是更好的只是post_id
在comment_table
,如果你需要“t3_”追加到它由于某种原因,这样做在SELECT
:
Select PT.ID, PT.title, concat('t3_', PT.id) link_id, CT.body
更新
既然是现在显然你正在使用BigQuery而不是mysql,上面的答案不适用。我不是很熟悉的BigQuery,但你可以尝试:
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table as PT
LEFT OUTER JOIN comment_table as CT
ON CT.link_id = 't3_' + PT.id
答
不知道的语法本身,但它似乎更为明智的事:在你的ON PT.ID = CT.concat('t3_', link_id)
条件
concat('t3_', PT.ID) = CT.link_id
答
关注。使用下面的SQL。
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table as PT
LEFT OUTER JOIN comment_table as CT
ON (CT.link_id = concat('t3_',PT.ID))
答
下面尝试使用BigQuery的标准SQL
#standardSQL
SELECT PT.ID, PT.title, CT.link_id, CT.body
FROM post_table AS PT
LEFT OUTER JOIN comment_table AS CT
ON CT.link_id = CONCAT('t3_', PT.ID)
错误消息应该告诉你在句法错误所在。编辑你的问题,并添加完整的错误信息。 –
为什么不直接在'link_id'中存储整数?你这样做的方式,你不能定义一个外键。如果您需要以其他方式加入,您将无法为连接使用索引。 –
在任何情况下,您都有两个表别名(PT和CT)切换到脚本的最后一行,这意味着您必须连接PT.ID而不是CT.link_id。尝试先改变它。 Luc – luc