php - WordPress中SQL查询中的对角线困惑

我在WordPress中执行SQL查询时遇到问题，我希望将每个Post ID都放在一行meta_键列中，但是我得到了几个不均匀的记录。

SELECT P.ID,
     IF(PM.meta_key = 'nombrem', PM.meta_value, NULL) AS nombrem,
     IF(PM.meta_key = 'generom', PM.meta_value, NULL) AS generom,
     IF(PM.meta_key = 'tiempom', PM.meta_value, NULL) AS tiempom,
     IF(PM.meta_key = 'urlm', PM.meta_value, NULL) AS urlm,
     IF(PM.meta_key = 'imagenm', PM.meta_value, NULL) AS imagenm
FROM K1nG_posts AS P
LEFT JOIN K1nG_postmeta AS PM ON ( P.ID = PM.post_id )
WHERE P.post_type = 'post'
AND (P.post_status = 'publish' OR P.post_status = 'private')  ORDER BY P.post_date DESC

通过在phpmyadmin中放置上面的代码，我希望一个post ID将每个meta_值都放在非对角水平列中，如图所示。

最佳答案

你看到他们在寻找条件聚合。为此，您需要添加一个GROUP BY子句，并用聚合函数将条件表达式包围起来：

SELECT P.ID,
    MAX(CASE WHEN PM.meta_key = 'nombrem' THEN PM.meta_value END) AS nombrem,
    MAX(CASE WHEN PM.meta_key = 'generom' THEN PM.meta_value END) AS generom,
    MAX(CASE WHEN PM.meta_key = 'tiempom' THEN PM.meta_value END) AS tiempom,
    MAX(CASE WHEN PM.meta_key = 'urlm'    THEN PM.meta_value END) AS urlm,
    MAX(CASE WHEN PM.meta_key = 'imagenm' THEN PM.meta_value END) AS imagenm
FROM
    K1nG_posts AS P
    LEFT JOIN K1nG_postmeta AS PM ON P.ID = PM.post_id
WHERE
    P.post_type = 'post'
    AND (P.post_status = 'publish' OR P.post_status = 'private')
GROUP BY P.ID, P.post_date
ORDER BY P.post_date DESC

关于php - WordPress中SQL查询中的对角线困惑，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58261054/