我想创建一个查询，通过查询包含有关其条目的相关信息的 3 个数据库来动态填充表。
wp_gf_entry、wp_gf_entry_meta和(理想情况下(使用wp_posts
查询正在使用wp_gf_entry表和wp_gf_entry_meta表。
SELECT DISTINCT
t2.id,
MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM
`wp_gf_entry_meta` t1
LEFT JOIN `wp_gf_entry` t2
ON (
t1.`entry_id` = t2.`id`
)
WHERE (
t2.`form_id` = 71
AND t2.`is_read` = 1 AND t2.`status` = 'active'
AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6')
)
GROUP BY t2.`id`
ORDER BY
t1.`id` ASC,
t1.`entry_id` ASC
如果条目已被"读取"并且条目未被"丢弃"，则此方法效果很好。
+-----+-----------+----------+-------+------------------------------+
| id | firstname | lastname | state | division |
+-----+-----------+----------+-------+------------------------------+
| 166 | Steve | Jobs | QLD | (null) |
| 253 | Bill | Clinton | NSW | (null) |
| 427 | Maria | McOldguy | VIC | Grand Master's (65 and over) |
| 447 | Some | Bloke | NSW | Master's (60-64) |
+-----+-----------+----------+-------+------------------------------+
但是这个查询仍然需要一个人每天多次与网站交互来处理条目(将它们标记为已读( - 因为无论条目是否已成功支付，条目仍然会写入数据库。
在这里，我想使用存储在每个条目的wp_gf_entry_meta行中的woocommerce_order_number键和值对来查询wp_posts表。
因此，将查询修改为此以将woocommerce_order_number拉入数组，我认为可以查询：
SELECT DISTINCT
t2.id,
MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM
`wp_gf_entry_meta` t1
LEFT JOIN `wp_gf_entry` t2
ON (
t1.`entry_id` = t2.`id`
)
WHERE (
t2.`form_id` = 71
AND t2.`is_read` = 1 AND t2.`status` = 'active'
AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')
)
GROUP BY t2.`id`
ORDER BY
t1.`id` ASC,
t1.`entry_id` ASC
上面的查询给了我一个带有 woocommerce 订单的列：
+-----+------+-----------+----------+-------+------------------------------+
| id | wcID | firstname | lastname | state | division |
+-----+------+-----------+----------+-------+------------------------------+
| 166 | 1 | Steve | Jobs | QLD | (null) |
| 253 | 2 | Bill | Clinton | NSW | (null) |
| 427 | 3 | Maria | McOldguy | VIC | Grand Master's (65 and over) |
| 447 | 4 | Some | Bloke | NSW | Master's (60-64) |
+-----+------+-----------+----------+-------+------------------------------+
但是，我想通过在 WHERE 语句中添加以下内容来消除人为因素
AND t1.`meta_value` IN (SELECT t3.ID FROM wp_posts t3 WHERE t3.ID = 'wcID' AND t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') AND t3.`post_type` = 'shop_order' )
导致此错误消息"警告：#1292 截断了不正确的双精度值："wcID'"。
而且我不太确定如何巧妙地处理这个最后的 AND 语句。
SQL 小提琴位置
有没有办法使用woocommerce_order_number别名 wcID 查询wp_posts表和 ID 列？
这在這裡由 Akina 回答 数据库管理员
下面是最终的工作 sql 查询：
SELECT
t2.id,
MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM wp_gf_entry_meta t1
LEFT JOIN wp_gf_entry t2
ON t1.entry_id = t2.id
LEFT JOIN ( SELECT t3.ID
FROM wp_posts t3
WHERE t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge')
AND t3.`post_type` = 'shop_order' ) t4
ON t1.`meta_key` = 'woocommerce_order_number' AND t1.meta_value = t4.ID
WHERE t2.`form_id` = 71
AND t2.`status` = 'active'
AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')
GROUP BY t2.id