时间戳

首页 > 解决方案 > 连接表中没有匹配的行将整行归零

sql - 连接表中没有匹配的行将整行归零

问题描述

没有错误消息只是丢失了行。我应该有 27 行。我丢失了 16 行到缺少的 _shipping 公司行和 6 行到缺少的 _shipping_address_2 行。

SELECT p.post_id, 
       FROM_ UNIXTIME(p.meta_value) AS ShipDate, 
       t1.meta_value AS CustomerFName, 
       t2.meta_value AS CustomerLName, 
       ifnull(t3.meta_value, '') AS CompanyName, 
       t4.meta_value AS AddressLine1, 
       ifnull(t5.meta_value, '') AS AddressLine2, 
       t6.meta_value AS City, 
       t7.meta_value AS State, 
       t8.meta_value AS Zip, 
       t9.meta_value AS Tax, 
       t10.meta_value AS shippingTax 

FROM    wsuwp.wp_1175_postmeta p 
LEFT JOIN wp_1175_postmeta t1 ON p.post_id = t1.post_id 
LEFT JOIN wp_1175_postmeta t2 ON p.post_id = t2.post_id 
LEFT JOIN wp_1175_postmeta t3 ON p.post_id = t3.post_id
LEFT JOIN wp_1175_postmeta t4 ON p.post_id = t4.post_id
LEFT JOIN wp_1175_postmeta t5 ON p.post_id = t5.post_id 
LEFT JOIN wp_1175_postmeta t6 ON p.post_id = t6.post_id 
LEFT JOIN wp_1175_postmeta t7 ON p.post_id = t7.post_id
LEFT JOIN wp_1175_postmeta t8 ON p.post_id = t8.post_id 
LEFT JOIN wp_1175_postmeta t9 ON p.post_id = t9.post_id 
LEFT JOIN wp_1175_postmeta t10 ON p.post_id = t10.post_id 

WHERE (p.post_id IN (SELECT ID FROM wp_1175_posts 
                     WHERE post_type = 'shop_order' AND post_status = 'wc-completed')
  AND  p.meta_key = '_date_completed'
  AND  p.meta_value BETWEEN UNIX_TIMESTAMP('2019-05-01 00:00:00') 
                        AND UNIX_TIMESTAMP('2019-06-01 00:00:00') )
  AND t1.meta_key = '_shipping_first_name' 
  AND t2.meta_key = '_shipping_last_name'
  AND t3.meta_key = '_shipping_company'
  AND t4.meta_key = '_shipping_address_1' 
  AND t5.meta_key = '_shipping_address_2' 
  AND t6.meta_key = '_shipping_city' 
  AND t7.meta_key = '_shipping_state' 
  AND t8.meta_key = '_shipping_postcode'
  AND t9.meta_key = '_order_tax' 
  AND t10.meta_key = '_order_shipping_tax' 

GROUP BY p.post_id;

标签： sqljoinmariadb

解决方案

使用条件聚合。就像是：

SELECT p.post_id,
       MAX(CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.value END) as shipping_first_name,
       MAX(CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.value END) as shipping_last_name,
       . . . -- continue for the rest of the columns
FROM  wsuwp.wp_1175_postmeta p JOIN
      wp_1175_postmeta pm
      ON p.post_id = pm.post_id 
WHERE p.post_id IN (SELECT pp.ID
                    FROM wp_1175_posts pp
                    WHERE pp.post_type = 'shop_order' AND pp.post_status = 'wc-completed'
                   ) AND
      p.meta_key = '_date_completed'
      p.meta_value BETWEEN UNIX_TIMESTAMP('2019-05-01') AND UNIX_TIMESTAMP('2019-06-01')
GROUP BY p.post_id;

推荐阅读