我正在尝试构建一个MySQL语句,该语句从两个MySQL表获取数据并显示其关联的数据。
我当前的问题是试图将" _billing_first_name"one_answers" _billing_last_name"字段连接到一个"计费全名"列
查询结果当前仅显示第一个计费名称,而不显示第二个串联值。
我选择的当前语句如下:
SELECT
wp_posts.ID,
wp_posts.post_title,
pm1.meta_value as 'Billing Address 1',
pm2.meta_value as 'Billing Address 2',
pm3.meta_value as 'Billing City',
pm4.meta_value as 'Billing State',
pm5.meta_value as 'Billing Postcode',
pm6.meta_value as 'Billing Email',
pm7.meta_value as 'Billing Phone',
pm8.meta_value as 'Billing Full Name',
pm10.meta_value as 'Shipping First Name',
pm11.meta_value as 'Shipping Last Name',
pm12.meta_value as 'Shipping Address 1',
pm13.meta_value as 'Shipping Address 2',
pm14.meta_value as 'Shipping City',
pm15.meta_value as 'Shipping State',
pm16.meta_value as 'Shipping Postcode',
pm17.meta_value as 'Order Shipping'
FROM wp_posts
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='_billing_address_1')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='_billing_address_2')
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='_billing_city')
LEFT JOIN wp_postmeta AS pm4 ON (wp_posts.ID = pm4.post_id AND pm4.meta_key='_billing_state')
LEFT JOIN wp_postmeta AS pm5 ON (wp_posts.ID = pm5.post_id AND pm5.meta_key='_billing_postcode')
LEFT JOIN wp_postmeta AS pm6 ON (wp_posts.ID = pm6.post_id AND pm6.meta_key='_billing_email')
LEFT JOIN wp_postmeta AS pm7 ON (wp_posts.ID = pm7.post_id AND pm7.meta_key='_billing_phone')
LEFT JOIN wp_postmeta AS pm8 ON (wp_posts.ID = pm8.post_id AND CONCAT_WS(' ',pm8.meta_key='_billing_first_name', pm8.meta_key='_billing_last_name'))
LEFT JOIN wp_postmeta AS pm10 ON (wp_posts.ID = pm10.post_id AND pm10.meta_key='_shipping_first_name')
LEFT JOIN wp_postmeta AS pm11 ON (wp_posts.ID = pm11.post_id AND pm11.meta_key='_shipping_last_name')
LEFT JOIN wp_postmeta AS pm12 ON (wp_posts.ID = pm12.post_id AND pm12.meta_key='_shipping_address_1')
LEFT JOIN wp_postmeta AS pm13 ON (wp_posts.ID = pm13.post_id AND pm13.meta_key='_shipping_address_2')
LEFT JOIN wp_postmeta AS pm14 ON (wp_posts.ID = pm14.post_id AND pm14.meta_key='_shipping_city')
LEFT JOIN wp_postmeta AS pm15 ON (wp_posts.ID = pm15.post_id AND pm15.meta_key='_shipping_state')
LEFT JOIN wp_postmeta AS pm16 ON (wp_posts.ID = pm16.post_id AND pm16.meta_key='_shipping_postcode')
LEFT JOIN wp_postmeta AS pm17 ON (wp_posts.ID = pm17.post_id AND pm17.meta_key='_order_shipping')
WHERE
wp_posts.post_type = 'shop_order' AND
wp_posts.post_status = 'wc-completed' AND
wp_posts.post_date > '2016-08-01 13:13:00' AND
wp_posts.post_date < '2017-05-31 13:13:00'
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC
对此的任何帮助将不胜感激,谢谢
SELECT
wp_posts.ID,
wp_posts.post_title,
pm1.meta_value as 'Billing Address 1',
pm2.meta_value as 'Billing Address 2',
pm3.meta_value as 'Billing City',
pm4.meta_value as 'Billing State',
pm5.meta_value as 'Billing Postcode',
pm6.meta_value as 'Billing Email',
pm7.meta_value as 'Billing Phone',
CONCAT_WS(' ',pm9.meta_value,pm8.meta_value) as 'Billing Full Name',
pm10.meta_value as 'Shipping First Name',
pm11.meta_value as 'Shipping Last Name',
pm12.meta_value as 'Shipping Address 1',
pm13.meta_value as 'Shipping Address 2',
pm14.meta_value as 'Shipping City',
pm15.meta_value as 'Shipping State',
pm16.meta_value as 'Shipping Postcode',
pm17.meta_value as 'Order Shipping'
FROM wp_posts
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='_billing_address_1')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='_billing_address_2')
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='_billing_city')
LEFT JOIN wp_postmeta AS pm4 ON (wp_posts.ID = pm4.post_id AND pm4.meta_key='_billing_state')
LEFT JOIN wp_postmeta AS pm5 ON (wp_posts.ID = pm5.post_id AND pm5.meta_key='_billing_postcode')
LEFT JOIN wp_postmeta AS pm6 ON (wp_posts.ID = pm6.post_id AND pm6.meta_key='_billing_email')
LEFT JOIN wp_postmeta AS pm7 ON (wp_posts.ID = pm7.post_id AND pm7.meta_key='_billing_phone')
LEFT JOIN wp_postmeta AS pm8 ON (wp_posts.ID = pm8.post_id AND pm8.meta_key='_billing_last_name')
LEFT JOIN wp_postmeta AS pm9 ON (wp_posts.ID = pm9.post_id AND pm9.meta_key='_billing_first_name')
LEFT JOIN wp_postmeta AS pm10 ON (wp_posts.ID = pm10.post_id AND pm10.meta_key='_shipping_first_name')
LEFT JOIN wp_postmeta AS pm11 ON (wp_posts.ID = pm11.post_id AND pm11.meta_key='_shipping_last_name')
LEFT JOIN wp_postmeta AS pm12 ON (wp_posts.ID = pm12.post_id AND pm12.meta_key='_shipping_address_1')
LEFT JOIN wp_postmeta AS pm13 ON (wp_posts.ID = pm13.post_id AND pm13.meta_key='_shipping_address_2')
LEFT JOIN wp_postmeta AS pm14 ON (wp_posts.ID = pm14.post_id AND pm14.meta_key='_shipping_city')
LEFT JOIN wp_postmeta AS pm15 ON (wp_posts.ID = pm15.post_id AND pm15.meta_key='_shipping_state')
LEFT JOIN wp_postmeta AS pm16 ON (wp_posts.ID = pm16.post_id AND pm16.meta_key='_shipping_postcode')
LEFT JOIN wp_postmeta AS pm17 ON (wp_posts.ID = pm17.post_id AND pm17.meta_key='_order_shipping')
WHERE
wp_posts.post_type = 'shop_order' AND
wp_posts.post_status = 'wc-completed' AND
wp_posts.post_date > '2016-08-01 13:13:00' AND
wp_posts.post_date < '2017-05-31 13:13:00'
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC