选择联接表的列值作为结果列名 [英] Selecting a join table's column values as result column name

问题描述

我有一个具有以下表格的WordPress数据库

I've a WordPress database with following tables

帖子

+--------------+
| Field        |
+--------------+
| ID           |
| post_author  |
| post_title   | 
| post_type    | 
+--------------+

postmeta

+--------------+
| Field        |
+--------------+
| meta_id      |
| post_id      |
| meta_key     |
| meta_value   |
+--------------+

并具有meta_key值latitude和longitude

如何编写SQL以将latitude和longitude显示为结果列名称?

How can I write SQL to show latitude and longitude as result column names?

+--------------+
| Field        |
+--------------+
| ID           |
| post_author  |
| post_title   |
| latitude     |
| longitude    | 
+--------------+

下面的查询是我的出发点

Below query is my starting point

SELECT ID, post_title, meta_key, meta_value FROM `wp_posts` 
LEFT JOIN `wp_postmeta` on ID=post_id 
WHERE post_type='place' AND (meta_key='latitude' OR meta_key='longitude') 
ORDER BY ID ASC

推荐答案

听起来像您想PIVOT latitude和longitude值.不幸的是，MySQL没有PIVOT函数，但是您可以使用带有CASE语句的聚合函数来复制它:

It sounds like you want to PIVOT the latitude and longitude values. Unfortunately, MySQL does not have a PIVOT function but you can replicate it using an aggregate function with a CASE statement:

SELECT p.ID, 
  p.post_title, 
  p.post_author,
  max(case when pm.meta_key='latitude' then pm.meta_value end) latitude,
  max(case when pm.meta_key='longitude' then pm.meta_value end) longitude
FROM `wp_posts` p
LEFT JOIN `wp_postmeta` pm
  on p.ID=pm.post_id 
WHERE p.post_type='place' 
  AND (pm.meta_key='latitude' OR pm.meta_key='longitude') 
GROUP BY p.ID, p.post_title, p.post_author
ORDER BY p.ID ASC

请参见带有演示的SQL提琴

这篇关于选择联接表的列值作为结果列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！