GROUP_CONCAT()内部的CONCAT()

Question

我有以下数据：

---------------------------------------------------------------------
|  id  |     date     |              order_items           |   qty   |
---------------------------------------------------------------------
| 1460 |  2017-03-24  |  Alfonso XO                        |    2    |
| 1460 |  2017-03-24  |  Godiva Dark 85% Cacao             |    3    |
| 1460 |  2017-03-24  |  Godiva Gold Rigid Ballotin 14pcs  |    5    | 
---------------------------------------------------------------------

通过这个查询：

SELECT 
   p.ID, 
   date(p.post_date) AS date, 
   i.order_item_name AS order_items
   max( CASE WHEN o.meta_key = '_qty' AND i.order_item_id = o.order_item_id THEN o.meta_value END ) AS qty
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name

然后是这个：

--------------------------------------------------------------
|  id  |     date     |               order_items            |
--------------------------------------------------------------
| 1460 |  2017-03-24  |  2 Alfonso XO                        |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao             |
| 1460 |  2017-03-24  |  5 Godiva Gold Rigid Ballotin 14pcs  | 
--------------------------------------------------------------

通过这一途径：

SELECT 
   p.ID, 
   date(p.post_date) AS date, 
   concat(max( CASE WHEN imeta.meta_key = '_qty' and items.order_item_id = imeta.order_item_id THEN imeta.meta_value END ), ' ', items.order_item_name) as order_items
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name

我的问题是，如何实现我的数据成为一列：

--------------------------------------------------------------
|  id  |     date     |               order_items            |
--------------------------------------------------------------
|      |              |  2 Alfonso XO                        |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao             |
|      |              |  5 Godiva Gold Rigid Ballotin 14pcs  |
--------------------------------------------------------------

我试图将我的concat()与group_concat()放在一起，但是我收到了一个错误：Invalid group function。

更新

给Giorgos Betsos爵士找个正确答案的大道具。

下面是我的查询：

SELECT ID, post_date, GROUP_CONCAT(order_items separator '\n') AS order_items, post_status, shipping_flight_number, letter_code, terminal, order_total, full_name
FROM (
   SELECT 
  p.ID, 
  date(p.post_date) AS post_date,
  p.post_status AS post_status,
  concat(max( CASE 
                 WHEN o.meta_key = '_qty' and 
                      i.order_item_id = o.order_item_id 
                    THEN o.meta_value 
              END ), ' ', i.order_item_name) as order_items,
  max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_flight_number,
  substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1) as letter_code,
  ( select terminal_id from wp_shipping_airlines where letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal,
  max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
  concat(max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ), ' ', max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END )) as full_name
FROM wp_posts AS p
LEFT JOIN wp_postmeta pm on p.ID = pm.post_id
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.post_type = 'shop_order' AND i.order_item_type = 'line_item' AND p.post_status != 'trash'
GROUP BY i.order_item_name, p.ID) AS t
GROUP BY ID, post_date, shipping_flight_number, letter_code, terminal, order_total, full_name

然而，在进一步调整我的查询时，我又提出了另一个问题：

1. I尝试在新查询中生成的数据上连接另一个数据：

-----------------------------------------------------------------------
|  id  |     date     |                   order_items                 |
-----------------------------------------------------------------------
|      |              |  2 Alfonso XO (2300551)                       |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao (2657010)            |
|      |              |  5 Godiva Gold Rigid Ballotin 14pcs (2421181) |
-----------------------------------------------------------------------

括号中的那些被称为SKU，它们可以在wp_postmeta表中找到，但它们通过p.ID和p.post_id 连接，但通过与p.post_type = 'product'连接。

我试着用concat_ws()代替concat_ws()，但没有运气。

SELECT ID, post_date, GROUP_CONCAT(order_items separator '\n') AS order_items, post_status, shipping_flight_number, letter_code, terminal, order_total, full_name
FROM (
   SELECT 
  p.ID, 
  date(p.post_date) AS post_date,
  p.post_status AS post_status,
  concat_ws(' ', max( CASE 
                 WHEN o.meta_key = '_qty' and 
                      i.order_item_id = o.order_item_id 
                    THEN o.meta_value 
              END ), i.order_item_name, ' -- ', max( CASE WHEN pm.meta_key = '_sku' and p.ID = pm.post_id and p.post_type = 'product' THEN pm.meta_value END )) as order_items,
  max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_flight_number,
  substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1) as letter_code,
  ( select terminal_id from wp_shipping_airlines where letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal,
  max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
  concat(max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ), ' ', max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END )) as full_name
FROM wp_posts AS p
LEFT JOIN wp_postmeta pm on p.ID = pm.post_id
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.post_type = 'shop_order' AND i.order_item_type = 'line_item' AND p.post_status != 'trash'
GROUP BY i.order_item_name, p.ID) AS t
GROUP BY ID, post_date, shipping_flight_number, letter_code, terminal, order_total, full_name

非常感谢你的帮助。

非常感谢。

-Eli

Answer 1

这应该是可行的：

SELECT ID, date, GROUP_CONCAT(order_items) AS order_items
FROM (
   SELECT 
      p.ID, 
      date(p.post_date) AS date, 
      concat(max( CASE 
                     WHEN imeta.meta_key = '_qty' and 
                          items.order_item_id = imeta.order_item_id 
                        THEN imeta.meta_value 
                  END ), ' ', items.order_item_name) as order_items
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name) AS t
GROUP BY ID, date

注意:您可以在GROUP_CONCAT中添加任何您喜欢的分隔符，例如：GROUP_CONCAT(order_items SEPARATOR '\n')。