mysql - 如何重构这个 MySQL 查询(2 个表)？

Question

我有这两个表:

TABLE: xg_posts

TABLE: xg_postmeta

然后，我这样做:

SELECT xg_posts.post_title,
xg_posts.post_content,
CASE WHEN xg_postmeta.meta_key="bgmp_address" THEN xg_postmeta.meta_value ELSE NULL END AS address,
CASE WHEN xg_postmeta.meta_key="bgmp_latitude" THEN xg_postmeta.meta_value ELSE NULL END AS latitude,
CASE WHEN xg_postmeta.meta_key="bgmp_longitude" THEN xg_postmeta.meta_value ELSE NULL END AS longitude
FROM xg_postmeta, xg_posts
WHERE xg_posts.post_type = "bgmp"
AND xg_postmeta.post_id = xg_posts.ID
AND (xg_postmeta.meta_key = "bgmp_address"
OR xg_postmeta.meta_key = "bgmp_latitude"
OR xg_postmeta.meta_key = "bgmp_longitude")
ORDER BY xg_posts.post_title ASC

得到这个: Result from mentioned query

但是，我需要的是包含所有数据的 1 ROW。例如。

post_title | post_content | address | longitude | latitude

A.C.A. San Lorenzo (Est. 21145) | AVDA. SAN MARTIN 2898,SAN LORENZO |-32.7161131 | -60.7314613

提前非常感谢您!!

Answer 1

您需要分组和聚合。请注意 max( ) 和 group by 子句:

SELECT  xg_posts.post_title,
        xg_posts.post_content,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_address" THEN xg_postmeta.meta_value ELSE NULL END) AS address,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_latitude" THEN xg_postmeta.meta_value ELSE NULL END) AS latitude,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_longitude" THEN xg_postmeta.meta_value ELSE NULL END AS longitude
FROM    xg_postmeta, xg_posts
WHERE   xg_posts.post_type = "bgmp"
    AND xg_postmeta.post_id = xg_posts.ID
    AND (xg_postmeta.meta_key = "bgmp_address"
    OR  xg_postmeta.meta_key = "bgmp_latitude"
    OR  xg_postmeta.meta_key = "bgmp_longitude")
GROUP BY xg_posts.post_title,
         xg_posts.post_content
ORDER BY xg_posts.post_title ASC

根据 post_content 的内容，您可能还需要对其应用 max 并将其从 group by 子句中删除:

SELECT  xg_posts.post_title,
        max(xg_posts.post_content) AS post_content,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_address" THEN xg_postmeta.meta_value ELSE NULL END) AS address,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_latitude" THEN xg_postmeta.meta_value ELSE NULL END) AS latitude,
        max(CASE WHEN xg_postmeta.meta_key="bgmp_longitude" THEN xg_postmeta.meta_value ELSE NULL END AS longitude
FROM    xg_postmeta, xg_posts
WHERE   xg_posts.post_type = "bgmp"
    AND xg_postmeta.post_id = xg_posts.ID
    AND (xg_postmeta.meta_key = "bgmp_address"
    OR  xg_postmeta.meta_key = "bgmp_latitude"
    OR  xg_postmeta.meta_key = "bgmp_longitude")
GROUP BY xg_posts.post_title
ORDER BY xg_posts.post_title ASC