mysql - 合并查询和行到列

Question

我有下面的查询，它获取我需要的行的 ID。

SELECT `wp_posts`.`ID`, `wp_posts`.`post_modified_gmt`, `wp_term_relationships`.`term_taxonomy_id`
FROM `wp_posts`
LEFT JOIN `wp_term_relationships` ON
`wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
AND `wp_term_relationships`.`term_taxonomy_id` = 14
AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
ORDER BY `wp_posts`.`post_modified` ASC;

+----+---------------------+------------------+
| ID | post_modified       | term_taxonomy_id |
+----+---------------------+------------------+
| 41 | 2013-04-25 16:02:43 |               14 |
| 43 | 2013-04-25 18:40:37 |               14 |
+----+---------------------+------------------+
2 rows in set (0.00 sec)

然后我遍历所有 ID 并使用 ID 运行下面的查询以匹配查询中的 post_id。

SELECT `wp_postmeta`.`post_id`, `wp_postmeta`.`meta_key`, `wp_postmeta`.`meta_value` 
FROM `wp_postmeta` 
WHERE `post_id` = 43 # ID from last query
AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'  
OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
OR `wp_postmeta`.`meta_key` = '_shipping_city'
OR `wp_postmeta`.`meta_key` = '_shipping_state'
OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
OR `wp_postmeta`.`meta_key` = '_shipping_country'
OR `wp_postmeta`.`meta_key` = '_order_total'
OR `wp_postmeta`.`meta_key` = '_order_shipping'
OR `wp_postmeta`.`meta_key` = '_order_discount'
OR `wp_postmeta`.`meta_key` = '_cart_discount'
OR `wp_postmeta`.`meta_key` = '_order_tax'
OR `wp_postmeta`.`meta_key` = '_order_shipping_tax');

+---------+----------------------+------------------+
| post_id | meta_key             | meta_value       |
+---------+----------------------+------------------+
|      43 | _shipping_country    | GB               |
|      43 | _shipping_first_name | Joe              |
|      43 | _shipping_last_name  | Bloggs           |
|      43 | _shipping_address_1  | 18 Street Name   |
|      43 | _shipping_address_2  |                  |
|      43 | _shipping_city       | Manchester       |
|      43 | _shipping_state      | Lancashire       |
|      43 | _shipping_postcode   | MM1 1MM          |
|      43 | _order_shipping      | 0.00             |
|      43 | _order_discount      | 0.00             |
|      43 | _cart_discount       | 0.00             |
|      43 | _order_tax           | 0.00             |
|      43 | _order_shipping_tax  | 0.00             |
|      43 | _order_total         | 224.00           |
+---------+----------------------+------------------+
14 rows in set (0.00 sec)

我想知道是否有办法合并这些查询，所以我将对服务器的查询量减半。

我不确定您是否可以这样做，但我想将 meta_key 值更改为列名并使用 meta_value 作为值？

最终结果示例

+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| ID | post_modified       | term_taxonomy_id | _shipping_country | _shipping_first_name | _shipping_last_name | _shipping_address_1 | _shipping_address_2 | _shipping_city | _shipping_state | _shipping_postcode | _order_shipping | _order_discount | _cart_discount | _order_tax | _order_shipping_tax | _order_total |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| 43 | 2013-04-25 18:40:37 |               14 | GB                | Joe                  | Bloggs              | 18 Street Name      |                     | Manchester     | Lancashire      | MM1 1MM            | 0.00            | 0.00            | 0.00           | 0.00       | 0.00                | 224.00       |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
Next row...
Next row...

任何帮助都会很棒。

谢谢

编辑

在 meewoK 的帮助下，这是适合我的查询。

SELECT (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_first_name' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_first_name',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_last_name' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_last_name',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_1' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_address_1',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_2' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_address_2',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_city' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_city',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_state' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_state',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_postcode' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_postcode',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_country' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_country',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_total' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_total',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_shipping' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_shipping',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_discount' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_discount',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_cart_discount' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_cart_discount',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_tax' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_tax',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_shipping_tax' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_shipping_tax',
    `wp_posts`.`ID`,
    `wp_posts`.`post_modified_gmt`,
    `wp_term_relationships`.`term_taxonomy_id`
FROM `wp_postmeta`, `wp_posts`
LEFT JOIN `wp_term_relationships` ON `wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
    AND `wp_term_relationships`.`term_taxonomy_id` = 14
    AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
    AND `post_id` = `wp_posts`.`ID`
    AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'
        OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
        OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
        OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
        OR `wp_postmeta`.`meta_key` = '_shipping_city'
        OR `wp_postmeta`.`meta_key` = '_shipping_state'
        OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
        OR `wp_postmeta`.`meta_key` = '_shipping_country'
        OR `wp_postmeta`.`meta_key` = '_order_total'
        OR `wp_postmeta`.`meta_key` = '_order_shipping'
        OR `wp_postmeta`.`meta_key` = '_order_discount'
        OR `wp_postmeta`.`meta_key` = '_cart_discount'
        OR `wp_postmeta`.`meta_key` = '_order_tax'
        OR `wp_postmeta`.`meta_key` = '_order_shipping_tax')
GROUP BY `wp_posts`.`ID`
ORDER BY `wp_posts`.`post_modified` ASC;

最终结果示例

+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| ID | post_modified       | term_taxonomy_id | _shipping_country | _shipping_first_name | _shipping_last_name | _shipping_address_1 | _shipping_address_2 | _shipping_city | _shipping_state | _shipping_postcode | _order_shipping | _order_discount | _cart_discount | _order_tax | _order_shipping_tax | _order_total |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| 43 | 2013-04-25 18:40:37 |               14 | GB                | Joe                  | Bloggs              | 18 Street Name      |                     | Manchester     | Lancashire      | MM1 1MM            | 0.00            | 0.00            | 0.00           | 0.00       | 0.00                | 224.00       |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
Next row...
Next row...

Answer 1

这需要一个连接才能让它在一个查询中工作。像这样:

  SELECT `wp_posts`.`ID`,
       `wp_posts`.`post_modified_gmt`,
       `wp_term_relationships`.`term_taxonomy_id`,
       `wp_postmeta`.`post_id`,
       `wp_postmeta`.`meta_key`,
       `wp_postmeta`.`meta_value`
FROM `wp_postmeta`, `wp_posts`
LEFT JOIN `wp_term_relationships` ON `wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
  AND `wp_term_relationships`.`term_taxonomy_id` = 14
  AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
  AND `post_id` = `wp_posts`.`ID`
  AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'
       OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
       OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
       OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
       OR `wp_postmeta`.`meta_key` = '_shipping_city'
       OR `wp_postmeta`.`meta_key` = '_shipping_state'
       OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
       OR `wp_postmeta`.`meta_key` = '_shipping_country'
       OR `wp_postmeta`.`meta_key` = '_order_total'
       OR `wp_postmeta`.`meta_key` = '_order_shipping'
       OR `wp_postmeta`.`meta_key` = '_order_discount'
       OR `wp_postmeta`.`meta_key` = '_cart_discount'
       OR `wp_postmeta`.`meta_key` = '_order_tax'
       OR `wp_postmeta`.`meta_key` = '_order_shipping_tax')
ORDER BY `wp_posts`.`post_modified` ASC;

编辑:

关于旋转的要求，我会使用以下内容。您将不得不为每个可能的 meta_key 硬编码一个案例....

select (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_first_name' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_first_name',
(CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_1' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_address_1',
(CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_2' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_address_2',
...

等等。