mySQL 查询返回 3 列，我想从连接成逗号分隔字符串的一列中获取值

Question

我需要从meta_value 列中获取相应meta_key 列中具有“shipping”的值，并将它们连接成逗号分隔的行。

我对 SQL 有点菜鸟，所以请保持温柔。

我当前的查询是这样的:

SELECT wp_postmeta.post_ID, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.ID, wp_posts.post_status 
FROM wp_postmeta 
CROSS JOIN wp_posts 
WHERE wp_postmeta.post_ID= wp_posts.ID 
  AND wp_posts.post_status LIKE 'wc-processing' 
  AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total') 

Answer 1

所以，这是你的查询(注意我是如何编写它的 - 我发现良好的 SQL 格式有助于阅读它):

SELECT  
    wp_postmeta.post_ID, 
    wp_postmeta.meta_key, 
    wp_postmeta.meta_value, 
    wp_posts.ID, 
    wp_posts.post_status 
FROM 
    wp_postmeta 
CROSS JOIN 
    wp_posts 
WHERE 
    wp_postmeta.post_ID = wp_posts.ID 
    AND wp_posts.post_status LIKE 'wc-processing' 
    AND (wp_postmeta.meta_key LIKE '%shipping%' OR wp_postmeta.meta_key LIKE '_order_total') 

这就是我认为您想要的(您使用 MySQL concat function ):

Select
   concat(
        wp_postmeta.meta_key, ',',
        wp_postmeta.meta_value
    ) as formatted_row
FROM 
    wp_postmeta 
INNER JOIN 
    wp_posts 
ON
    wp_postmeta.post_ID = wp_posts.ID 
WHERE 
    wp_posts.post_status LIKE 'wc-processing' 
    AND (
           wp_postmeta.meta_key LIKE '%shipping%' 
           OR wp_postmeta.meta_key LIKE '_order_total'
        ) 

另请注意:单引号和双引号在 different databases 中的含义不同.