无法使复杂的mysql查询正常工作 [英] Can't get a complicated mysql query to work

问题描述

我正在尝试编写mysql查询，但遇到了一些问题.我正在尝试从我的Wordpress数据库中查询WooCommerce数据.基本发票数据存储在wp_posts表中，其余数据存储在wp_postmeta表中.现在，wp_posts表中的1个inovice指向wp_postmeta表中的多个项目.这是一个例子.

I'm trying to write a mysql query and I'm having some issues with it. I'm trying to query WooCommerce data out of my Wordpress database. Basic invoice data is stored in the wp_posts table and the rest of the data is stored in the wp_postmeta table. Now 1 inovice in the wp_posts table points to multiple items in the wp_postmeta table. Here is an example.

WP_POSTS

----------------------------------------------------
ID           STATUS                 Date
----------------------------------------------------
0001         OPEN                   01/01/2000
0002         OPEN                   01/01/2000
0003         CLOSED                 01/02/2000

WP_POSTMETA

WP_POSTMETA

--------------------------------------------------------------------------
ID        POST_ID               META_KEY                META_VALUE
--------------------------------------------------------------------------
0001      0001                  CustomerLN              Test
0002      0001                  CustomerFN              Tester
0003      0001                  Payment_Type            PayPal
0004      0001                  Invoice_Total           $200
0005      0002                  CustomerLN              Doe
0006      0002                  CustomerFN              John
0007      0002                  Payment_Type            CC-Mastercard
0008      0002                  Invoice_Total           $1000

我有一个基本查询，可以从wp_posts表中提取数据，但是我不知道如何根据META_KEY值从第二个表中提取数据.

I've got a basic query that pulls the data in from the wp_posts table but I can't figure out how to pull data from the second table based on the META_KEY value.

任何帮助都会很棒.预先感谢.

Any help would be great. Thanks in advance.

推荐答案

欢迎使用 实体-属性-值 世界.

Welcome to the Entity-Attribute-Value world.

要正常表示结果集，可能不仅需要JOIN这两个表，还需要PIVOT结果集.您可以通过类似

To have a normal representation of the resultset you might need not only JOIN these two tables but also PIVOT the resultset. You can do that with a query like

SELECT p.id, p.status,
       MAX(CASE WHEN m.meta_key = 'CustomerLN'    
                THEN m.meta_value END) customer_last_name,
       MAX(CASE WHEN m.meta_key = 'CustomerFN'    
                THEN m.meta_value END) customer_firt_name,
       MAX(CASE WHEN m.meta_key = 'Payment_Type'  
                THEN m.meta_value END) payment_type,
       MAX(CASE WHEN m.meta_key = 'Invoice_Total' 
                THEN m.meta_value END) invoice_total
 FROM wp_posts p LEFT JOIN wp_postmeta m
   ON p.id = m.post_id
 GROUP BY p.id, p.status

示例输出:

+------+--------+--------------------+--------------------+---------------+---------------+
| id   | status | customer_last_name | customer_firt_name | payment_type  | invoice_total |
+------+--------+--------------------+--------------------+---------------+---------------+
|    1 | OPEN   | Test               | Tester             | PayPal        | $200          |
|    2 | OPEN   | Doe                | John               | CC-Mastercard | $1000         |
|    3 | CLOSED | NULL               | NULL               | NULL          | NULL          |
+------+--------+--------------------+--------------------+---------------+---------------+

这里是 SQLFiddle 演示

Here is SQLFiddle demo

现在要能够根据元键和元值过滤记录，您将必须使用HAVING子句

Now to be able to filter your records based on meta keys and meta values you'll have to use HAVING clause

例如，如果您要获取由客户Jhon Doe开具的发票

For example if you want to get invoices made by customer Jhon Doe

SELECT p.id, p.status,
       MAX(CASE WHEN m.meta_key = 'CustomerLN'    
                THEN m.meta_value END) customer_last_name,
       MAX(CASE WHEN m.meta_key = 'CustomerFN'    
                THEN m.meta_value END) customer_first_name,
       MAX(CASE WHEN m.meta_key = 'Payment_Type'  
                THEN m.meta_value END) payment_type,
       MAX(CASE WHEN m.meta_key = 'Invoice_Total' 
                THEN m.meta_value END) invoice_total
 FROM wp_posts p LEFT JOIN wp_postmeta m
   ON p.id = m.post_id
 GROUP BY p.id, p.status
HAVING customer_last_name = 'Doe'
   AND customer_first_name = 'John'

输出:

+------+--------+--------------------+---------------------+---------------+---------------+
| id   | status | customer_last_name | customer_first_name | payment_type  | invoice_total |
+------+--------+--------------------+---------------------+---------------+---------------+
|    2 | OPEN   | Doe                | John                | CC-Mastercard | $1000         |
+------+--------+--------------------+---------------------+---------------+---------------+

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于无法使复杂的mysql查询正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！