将子查询的结果插入带有常量的表中 [英] Insert results of subquery into table with a constant

问题描述

相关表格的概要如下:

我有一个表，我们称之为join，它有两列，都是其他表的外键.让我们调用两列 userid 和 buildingid，这样 join 看起来像

I have a table, lets call it join, that has two columns, both foreign keys to other tables. Let's call the two columns userid and buildingid so join looks like

+--------------+
| join         |
|--------------|
|userid        |
|buildingid    |
+--------------+

我基本上需要在这个表中插入一堆行.通过在此表中具有多个条目，每个用户将被分配到多个建筑物.因此，用户 13 可能通过以下方式分配到建筑物 1、2 和 3

I basically need to insert a bunch of rows into this table. Each user will be assigned to multiple buildings by having multiple entries in this table. So user 13 might be assigned to buildings 1, 2, and 3 by the following

13 1
13 2
13 3

如果建筑物编号不变，我正在尝试弄清楚如何在查询中执行此操作，也就是说，我将一组人分配到同一建筑物.基本上，(这是错误的)我想做

I'm trying to figure out how to do this in a query if the building numbers are constant, that is, I'm assigning a group of people to the same buildings. Basically, (this is wrong) I want to do

insert into join (userid, buildingid) values ((select userid from users), 1)

有意义吗?我也试过使用

Does that make sense? I've also tried using

select 1

我遇到的错误是子查询返回多个结果.我还尝试创建一个连接，基本上是使用静态选择查询，但也未成功.

The error I'm running into is that the subquery returns more than one result. I also attempted to create a join, basically with a static select query that was also unsuccessful.

有什么想法吗?

谢谢，克里斯

推荐答案

几乎！当您想插入查询的值时，不要尝试将它们放在 values 子句中.insert 可以将 select 作为值的参数！

Almost! When you want to insert to values of a query, don't try to put them in the values clause. insert can take a select as an argument for the values!

insert into join (userid, buildingid)
select userid, 1 from users

此外，本着学习更多的精神，您可以使用以下语法创建一个不存在的表:

Also, in the spirit of learning more, you can create a table that doesn't exist by using the following syntax:

select userid, 1 as buildingid
into join
from users

不过，这仅在表不存在的情况下才有效，但这是创建表副本的一种快速而肮脏的方式！

That only works if the table doesn't exist, though, but it's a quick and dirty way to create table copies!

这篇关于将子查询的结果插入带有常量的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！