mysql - 从同一列中选择特定值并显示在不同的列中

Question

我试图在 sql 查询中显示同一个表中的两个值。该表包含 2 个具有这些值的键。

目前，我做了这个

SELECT p.post_title, t.name, pm.meta_value AS address, pm.meta_value AS id
FROM `wp_posts` p
  LEFT JOIN wp_term_relationships trr ON p.ID = trr.object_id
  LEFT JOIN wp_term_taxonomy tax ON trr.term_taxonomy_id = tax.term_taxonomy_id
  LEFT JOIN wp_terms t ON tax.term_taxonomy_id = t.term_id
  LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE
  p.post_type = 'network-type' AND t.slug = 'atm' AND
  pm.meta_key IN ('network_address', 'network_branch_atm_id')

但这表明

| post_title | name | address   | id        |
---------------------------------------------
| Post name  | bla  | 12        | 12        |
| Post name  | bla  | address 1 | address 1 |

postmeta 表有这样的值

| meta_id | post_id | meta_key              | meta_value |
----------------------------------------------------------
| 1212    | 323     | network_address       | address 1  |
| 1212    | 323     | network_branch_atm_id | 12         |

我尝试使用 CASE 但出现错误(不允许用户执行)。

我应该改变什么才能得到

| post_title | name | address   | id        |
---------------------------------------------
| Post name  | bla  | address 1 | 12        |

Answer 1

聚合帖子标题名称，然后导出地址和 ID。您遇到的困难(我也在 wm_postmeta 表中发现)是信息存储为键和值。它可能需要一些按摩才能按照您希望的方式从 Wordpress 架构中提取数据。

SELECT
    p.post_title,
    t.name,
    MAX(CASE WHEN pm.meta_key = 'network_address'       THEN pm.meta_value END) AS address,
    MAX(CASE WHEN pm.meta_key = 'network_branch_atm_id' THEN pm.meta_value END) AS id
FROM wp_posts p
LEFT JOIN wp_term_relationships trr
    ON p.ID = trr.object_id
LEFT JOIN wp_term_taxonomy tax
    ON trr.term_taxonomy_id = tax.term_taxonomy_id
LEFT JOIN wp_terms t
    ON tax.term_taxonomy_id = t.term_id
LEFT JOIN wp_postmeta pm
    ON p.ID = pm.post_id
WHERE
    p.post_type = 'network-type' AND
    t.slug = 'atm' AND
    pm.meta_key IN ('network_address', 'network_branch_atm_id')
GROUP BY
    p.post_title,
    t.name;