具有公共选择的SQL多重插入

Question

我必须将数据插入到具有以下模式的表中

INSERT INTO tablename (a, b) VALUES (
    (123, (SELECT foo FROM someothertable WHERE some_condition)),
    (456, (SELECT foo FROM someothertable WHERE some_condition)),
    (789, (SELECT foo FROM someothertable WHERE some_condition)),
    ...

对于b列，所有插入的行都具有相同的值，我想将其因子化。我可以手动执行subselect并将值粘贴到中，但这将破坏我正在编写的脚本集中的封装。

我可以在同一个查询中用纯SQL执行此操作吗？

Answer 1

INSERT INTO tablename (a, b)
SELECT X.bar, S.foo
FROM someothertable S
   CROSS JOIN
   (SELECT 123 AS bar UNION ALL SELECT 456 UNION ALL SELECT 789) X
WHERE some_condition

Answer 2

用该值声明一个变量，并在插入时使用它。

假设foo是`varchar（10），它将是这样的：

declare @toInsert varchar(10)
select @toInsert = foo FROM someothertable WHERE some_condition
INSERT INTO tablename (a, b) VALUES (
    (123, @toInsert),
    (456, @toInsert),
    (789, @toInsert),

Answer 3

INSERT INTO tablename (a, b) 
  SELECT a, b
  FROM
      ( SELECT 123 AS a UNION
        SELECT 456 UNION
        ...  
        SELECT 789 
      ) AS aa
    CROSS JOIN 
      ( SELECT foo AS b FROM someothertable WHERE some_condition ) AS bb

Answer 4

这就是我的意思：

create table tableName (
a varchar(50),
b varchar(50))
create table someothertable (
keyToTableName varchar(50),
someOtherA varchar(50))
insert into someothertable values('a', 'otherA')
insert into someothertable values('b', 'otherB')
insert into tableName
select 'a', someOtherA from someothertable where keyToTableName='a' UNION
select 'b', someOtherA from someothertable where keyToTableName='b' 

Answer 5

这里有两种方法，它们都可以避免重复子选择，但并不严格地"在一个查询中"：

1） 使用临时变量

SET @b = SELECT foo FROM someothertable WHERE somecondition;
INSERT INTO tablename(a, b) VALUES (
  (1, @b),
  (2, @b),
  ...

2） 使用insert设置a列，然后使用update设置b列。这可以转移到临时表中。

CREATE TEMPORARY TABLE tmp LIKE tablename;
insert into tmp(a) values (1),(2),...
update tmp set b = select foo from someothertable where some_condition;
insert into tablename(a,b) select * from tmp;