问题描述
我正在尝试编写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查询正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！