mysql - 连接 2 个结果集以替换列中的值

Question

我有 2 个 SQL 结果数据集，需要将它们合并为一个，如下所示:

Final Merged Table
-------------
320 Sydney
321 Brisbane
322 Melbourne

实现此目的的 SQL 稍微复杂一些，因为每个数据列都作为针对用户 ID 的单独记录保存在数据库中(由于 Wordpress 及其存储“meta_value”数据的方式)

第一个结果集:

User Table (A)
-------------
320 119 
321 120 
322 121 

实现方式:

SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 

INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 

WHERE `form_id` = 9 AND  field_id = 219

第二个结果集:

Location Table (B)
-------------
119 Sydney
120 Brisbane
121 Melbourne

实现方式:

SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 

INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 

WHERE `form_id` = 10

MySQL 不断告诉我，我的最终连接代码在第 7 行“INNER JOIN”处有错误:(以上2条SQL语句工作正常)

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 9 AND  field_id = 219 ) AS A 

INNER JOIN

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 10) AS B 

ON A.city = B.id

任何人都可以告诉我如何实现这一目标吗？

Answer 1

你们很接近。简短的答案是您不需要在 join 子句中使用 SELECT * FROM，只需指定(派生)表本身:

SELECT * 

FROM (SELECT wp_frm_items.id, meta_value AS city  FROM `wp_frm_items` 
INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
WHERE `form_id` = 9 AND  field_id = 219 ) AS A 

INNER JOIN
    (SELECT wp_frm_items.id, meta_value FROM `wp_frm_items` 
    INNER JOIN `wp_frm_item_metas` on wp_frm_item_metas.item_id = wp_frm_items.id 
    WHERE `form_id` = 10) AS B 

ON A.city = B.id