无法让复杂的 mysql 查询工作 [英] Can't get a complicated mysql query to work
问题描述
我正在尝试编写一个 mysql 查询，但遇到了一些问题.我正在尝试从我的 Wordpress 数据库中查询 WooCommerce 数据.基本发票数据存储在 wp_posts 表中，其余数据存储在 wp_postmeta 表中.现在 wp_posts 表中的 1 个 inovice 指向 wp_postmeta 表中的多个项目.这是一个例子.
WP_POSTS
<前>--------------------------------------------------——ID 状态 日期--------------------------------------------------——0001 开放 01/01/20000002 开放 01/01/20000003 关闭 01/02/2000WP_POSTMETA
<前>--------------------------------------------------------------------------ID POST_ID META_KEY META_VALUE--------------------------------------------------------------------------0001 0001 客户LN测试0002 0001 客户FN测试仪0003 0001 Payment_Type 贝宝0004 0001 发票_总计 200 美元0005 0002 客户LN Doe0006 0002 客户FN 约翰0007 0002 Payment_Type CC-Mastercard0008 0002 发票_总计 1000 美元我有一个从 wp_posts 表中提取数据的基本查询，但我不知道如何根据 META_KEY 值从第二个表中提取数据.
任何帮助都会很棒.提前致谢.
欢迎来到 实体-属性-值世界.
要对结果集进行正常表示，您可能不仅需要 JOIN 这两个表，还需要 PIVOT 结果集.你可以用像
SELECT p.id, p.status,MAX(案例当 m.meta_key = 'CustomerLN'然后 m.meta_value END) customer_last_name,MAX(案例当 m.meta_key = 'CustomerFN'然后 m.meta_value END) customer_firt_name,MAX(案例当 m.meta_key = 'Payment_Type'然后 m.meta_value END) payment_type,MAX(案例当 m.meta_key = 'Invoice_Total'然后 m.meta_value END) invoice_total从 wp_posts p LEFT JOIN wp_postmeta mON p.id = m.post_idGROUP BY p.id, p.status示例输出:
<前>+------+--------+------------+--------------------+---------------+--------------+|身份证 |状态 |客户姓氏 |客户名称 |付款类型|invoice_total |+------+--------+------------+--------------------+---------------+--------------+|1 |开放 |测试 |测试仪 |贝宝 |200 美元 ||2 |开放 |母鹿 |约翰 |CC-万事达卡|1000 美元 ||3 |关闭 |空 |空 |空 |空 |+------+--------+------------+--------------------+---------------+--------------+这是SQLFiddle 演示
<小时>现在为了能够根据元键和元值过滤您的记录，您必须使用 HAVING 子句
例如，如果您想获得客户 Jhon Doe 开具的发票
SELECT p.id, p.status,MAX(案例当 m.meta_key = 'CustomerLN'然后 m.meta_value END) customer_last_name,MAX(案例当 m.meta_key = 'CustomerFN'然后 m.meta_value END) customer_first_name,MAX(案例当 m.meta_key = 'Payment_Type'然后 m.meta_value END) payment_type,MAX(案例当 m.meta_key = 'Invoice_Total'然后 m.meta_value END) invoice_total从 wp_posts p LEFT JOIN wp_postmeta mON p.id = m.post_idGROUP BY p.id, p.statusHAVING customer_last_name = 'Doe'AND customer_first_name = '约翰'输出:
<前>+------+--------+------------+---------------------+---------------+---------------+|身份证 |状态 |客户姓氏 |customer_first_name |付款类型|invoice_total |+------+--------+------------+---------------------+---------------+---------------+|2 |开放 |母鹿 |约翰 |CC-万事达卡|1000 美元 |+------+--------+------------+---------------------+---------------+---------------+这是SQLFiddle 演示
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
-------------------------------------------------------------------------- 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
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.
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
Sample output:
+------+--------+--------------------+--------------------+---------------+---------------+ | 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 | +------+--------+--------------------+--------------------+---------------+---------------+
Here is SQLFiddle demo
Now to be able to filter your records based on meta keys and meta values you'll have to use HAVING clause
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'
Output:
+------+--------+--------------------+---------------------+---------------+---------------+ | id | status | customer_last_name | customer_first_name | payment_type | invoice_total | +------+--------+--------------------+---------------------+---------------+---------------+ | 2 | OPEN | Doe | John | CC-Mastercard | $1000 | +------+--------+--------------------+---------------------+---------------+---------------+
Here is SQLFiddle demo
这篇关于无法让复杂的 mysql 查询工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
