如何使用其他参数执行SQL INSERT CROSS JOIN

Question

我正在编写一个将数据插入表的查询。该查询必须从两个单独的表中选择某些id以及传入的参数。我可以通过使用CROSS JOIN从两个独立的表中获取数据。然而，我不确定如何传入字符串作为我的第三个参数。下面是我的声明:

INSERT INTO TABLE1 (T2_ID, T3_ID)
SELECT T2_ID, T3_ID
FROM (SELECT T2_ID FROM TABLE2 WHERE TABLE2.NAME = 'BOB') AS T1
CROSS JOIN(SELECT T3_ID FROM TABLE3 WHERE TABLE3.DATA = 'BLARGH') AS T2;

我得到的ID，我需要从交叉连接回来，但我也需要在一个参数，这是一个字符串值。我怎么把这个也传递进去呢?

Answer 1

你可以在select:

中添加一个字符串

INSERT INTO TABLE1 (T2_ID, T3_ID, <col>)
SELECT T2_ID, T3_ID, 'a string' 
FROM (SELECT T2_ID FROM TABLE2 
      WHERE TABLE2.NAME = 'BOB') AS T1
CROSS JOIN(SELECT T3_ID 
           FROM TABLE3 WHERE TABLE3.DATA = 'BLARGH') AS T2;

严格来说，你不需要子选择:

INSERT INTO TABLE1 (T2_ID, T3_ID, <col>)
SELECT T2_ID, T3_ID, 'a string' 
FROM TABLE2 AS T1 
CROSS JOIN TABLE3 AS T2
WHERE T1.NAME = 'BOB'
  AND T2.DATA = 'BLARGH'