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

这应该比在sql server上更容易实现

create table if not exists test(col1 varchar(10),col2 varchar(10), col3 varchar(10));
insert into test values("this","is","test");
select concat(col1,",",col2,",",col3) as csvCol from test

交付

csvCol
this,is,test

你可以使用 GROUP_CONCAT 把线连在一起。正如@kermit指出的,您应该使用 INNER JOIN 不是一个 CROSS JOIN . 我想您希望将订单总额与发货明细分开,所以您还需要使用条件聚合。此查询应执行您希望的操作:

SELECT m.post_ID
     , GROUP_CONCAT(CASE WHEN m.meta_key LIKE '%shipping%' THEN m.meta_value END) AS shipping_info
     , MAX(CASE WHEN m.meta_key = '_order_total' THEN m.meta_value ELSE 0 END) AS order_total
FROM wp_postmeta m
INNER JOIN wp_posts p ON m.post_ID = p.ID 
WHERE p.post_status LIKE 'wc-processing' 
  AND (m.meta_key LIKE '%shipping%' OR m.meta_key LIKE '_order_total')

所以,这是您的查询(请注意我是如何编写的—我发现良好的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 .