返回值交叉连接 [英] Return value cross join
问题描述
我有两个表,一个是表#1
,其中包含用户信息,电子邮件,密码等。
I have two tables, one is a table #1
contains user information, email, password, etc..
另一个表#2包含项目信息
the other table #2 contains item information
当我在表#2
中插入时,并且然后使用returning语句,收集插入的内容(返回自动值以及其他信息),我还需要从表#1
返回信息。
when I do a insert into table #2
, and then use the returning statement, to gather what was inserted (returning auto values as well as other information), I also need to return information from table #1
.
(请使用语法)
示例:
insert into table #1(item,user) values('this item','the user')
returning *, select * from table 2 where table #1.user = table #2.user)
换句话说,在插入之后,我还需要返回插入的值作为有关插入数据的用户的信息。
in other words, after the insert I need to return the values inserted, as well as the information about the user who inserted the data.
这可能吗?
我想出的是在returning子句中使用一堆子查询语句。
the only thing I came up with is using a whole bunch of subquery statements in the returning clause. there has to be a better way.
推荐答案
我建议使用 修改数据的CTE (Postgres 9.1或更高版本):
I suggest a data-modifying CTE (Postgres 9.1 or later):
WITH ins AS (
INSERT INTO tbl1(item, usr)
VALUES('this item', 'the user')
RETURNING usr
)
SELECT t2.*
FROM ins
JOIN tbl2 t2 USING (usr)
使用列名 usr
代替 user
,这是保留字。
Working with the column name usr
instead of user
, which is a reserved word.
这篇关于返回值交叉连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!