MYSQL:使用来自两个子查询的数据创建一个插入

Question

我知道我可以在从数据库中选择行后使用子查询进行插入。

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id, term_order)
SELECT ID, 8363, 0 FROM
( SELECT DISTINCT
wp_43_posts.ID
FROM
wp_43_posts
INNER JOIN wp_43_term_relationships ON wp_43_term_relationships.object_id = wp_43_posts.ID
INNER JOIN wp_43_term_taxonomy ON wp_43_term_taxonomy.term_taxonomy_id = wp_43_term_relationships.term_taxonomy_id
WHERE  (wp_43_term_taxonomy.term_id = 4613 OR wp_43_term_taxonomy.term_id = 4615) AND wp_43_term_taxonomy.term_id != 8363 ) posts

但是，如果不是硬编码 8363，我需要从另一个查询中派生它呢？例如，我需要从中派生它

SELECT
wp_43_term_taxonomy.term_taxonomy_id
FROM
wp_43_term_taxonomy
WHERE
wp_43_term_taxonomy.term_id = 8363

Answer 1

如果用 () 中的第二个查询替换常量，它应该可以工作。像这样:

INSERT INTO wp_43_term_relationships (object_id, term_taxonomy_id,term_order)
SELECT ID,
(SELECT wp_43_term_taxonomy.term_taxonomy_id FROM wp_43_term_taxonomy WHERE wp_43_term_taxonomy.term_id = 8363),
0 FROM
... remaining of the original ...

至少如果你只需要 1 个值，否则最好将这个额外的表与其他表连接起来。