MySQL从左连接表插入一列的所有行？

Question

我在使用 INSERT 语句时遇到困难，在该语句中我提供了前四列的值。第五列(最后一列)应该连接另一个表的两列。该部分工作正常，但仅适用于一行。两个表都有 12 行(因此有 12 个文字值)，但是如何使连接的子查询返回我正在尝试的一列的所有行插入(从而与其他列中的值相对应)？

尝试了很多方法，但现在是这样的......

    INSERT INTO properties (property_id, condo_type, pets, internet, owner_name)
      SELECT 
    '301S', '207S', '1100T', '1201S', '317T', '110S', '1010S', '409T', '505T', '1005S', '656S', '942S' AS property_id,
    'SandsOF3BR', 'SandsOF3BR', 'SandsOF2BR', 'Tides3BR', 'SandsOF2BR', 'Tides2BR', 'SandsOF2BR', 'SandsOF2BR', 'Tides2BR', 'Tides3BR', 'SandsOF2BR', 'SandsOF3BR' AS condo_type,
    1,1,0,0,0,0,0,1,1,1,1,0 AS pets,
    1,1,0,1,1,1,0,1,0,1,0,0 AS internet,
    CONCAT(owner_first_name, ' ', owner_last_name) AS owner_name
      FROM owners LEFT JOIN properties 
        ON owners.property_id = properties.property_id
      WHERE owners.property_id = properties.property_id;

基本上，属性表和所有者表在各自的“property_id”列中都有数据，反射(reflect)哪个属性属于哪个所有者。我的意思是，我是否必须创建一个过程来循环第二个表，或者是否有更传统的方法？或者我是否会错误地填充此列？请帮忙，谢谢...任何人??

Answer 1

INSERT INTO properties
            (property_id,   /*here*/
             condo_type,    /*you*/
             pets,          /*name*/
             internet,      /*5*/
             owner_name)    /*columns*/

SELECT '301S',              /*here you name way more than the 5 columns*/
       '207S',              /*from your insert statement.*/
       '1100T',              
       '1201S',             /*So, where to put them? MySQL doesn't know*/
       '317T',              /*That's why you get an error.*/
       '110S',
       '1010S',
       '409T',
       '505T',
       '1005S',
       '656S',
       '942S'                                         AS property_id,
       'SandsOF3BR',
       'SandsOF3BR',
       'SandsOF2BR',
       'Tides3BR',
       'SandsOF2BR',
       'Tides2BR',
       'SandsOF2BR',
       'SandsOF2BR',
       'Tides2BR',
       'Tides3BR',
       'SandsOF2BR',
       'SandsOF3BR'                                   AS condo_type,
       1,
       1,
       0,
       0,
       0,
       0,
       0,
       1,
       1,
       1,
       1,
       0                                              AS pets,
       1,
       1,
       0,
       1,
       1,
       1,
       0,
       1,
       0,
       1,
       0,
       0                                              AS internet,
       Concat(owner_first_name, ' ', owner_last_name) AS owner_name
FROM   owners
       LEFT JOIN properties
              ON owners.property_id = properties.property_id
WHERE  owners.property_id = properties.property_id;  

这是两列吗

       Concat(owner_first_name, ' ', owner_last_name) AS owner_name

来自所有者表？如果是，那么您为什么要加入 properties 表呢？没必要。

这样写:

INSERT INTO properties
            (property_id,
             condo_type,
             pets,
             internet,
             owner_name)
SELECT 'value1',
       'value2',
       'value3',
       'value4',
       Concat(owner_first_name, ' ', owner_last_name)
FROM   owners
;  

这会将所有者的所有行(当然只是 2 列)插入到属性中。每行的前 4 列都是相同的，因为它们只是字符串。

如果您想将静态字符串插入表中，您可以编写多个语句或在一个语句中但指定多行，如下所示:

INSERT INTO foo (col1, col2) VALUES
(1, 2), /*first row*/
(3, 4); /*second row*/

编辑:

要结合这两种方法，您必须编写如下内容:

 INSERT INTO properties
            (property_id,
             condo_type,
             pets,
             internet,
             owner_name)
SELECT 
       CASE owners.property_id WHEN 1 THEN 'value1'
                               WHEN 2 THEN 'value2'
       END AS column1,
       CASE WHEN owner_first_name = 'fancy' AND owner_last_name = 'pants' THEN 'value3'
       ELSE 'value4' END AS column2,
       Concat(owner_first_name, ' ', owner_last_name) AS owner_name
FROM   owners
       LEFT JOIN properties
              ON owners.property_id = properties.property_id
WHERE  owners.property_id = properties.property_id;  

不太实用;)