php - 具有 2 个条件的同一张表上的 SQL JOIN

Question

我需要将 2 列合并为 1 行。列之间的键是 post_id。

这是我的表格(postmeta):

post_id     meta_key    meta_value
36          cp_state    California
37          cp_state    Illinois
38          cp_state    California
39          cp_state    California
40          cp_state    California
36          cp_city     Los Angeles
37          cp_city     Chicago
38          cp_city     San Diego
39          cp_city     San Diego
40          cp_city     Los Angeles

这是我试图输出的内容:

post_id     state       city
36          California  Los Angeles
37          Illinois    Chicago
38          California  San Diego
39          California  San Diego
40          California  Los Angeles

这是我使用的 SQL，但得到了大量的结果:

SELECT a.post_id AS pID, a.meta_value AS state, b.meta_value AS city
FROM $wpdb->prefix"."postmeta AS a
LEFT JOIN $wpdb->prefix"."postmeta AS b
ON b.post_id = a.post_id
WHERE a.meta_key = 'cp_state'
AND b.meta_key = 'cp_city'

在我使用的实时 Wordpress 表中，显然还有数百个其他 meta_key，所以我需要它来过滤这 2 个 meta_key (cp_city, cp_state)

Answer 1

你应该使用 INNER JOIN 而不是 LEFT JOIN

SELECT a.post_id AS pID, a.meta_value AS state, b.meta_value AS city
FROM       $wpdb->prefix"."postmeta AS a
INNER JOIN $wpdb->prefix"."postmeta AS b
ON b.post_id = a.post_id
WHERE a.meta_key = 'cp_state'
AND b.meta_key = 'cp_city'