mysql - 选择具有可选条件的查询

Question

我正在尝试实现情况 3:

情况 1: 客户已注册但尚未下订单。查询给出案例 1 的结果。

    SELECT c.customers_id, 
       c.customers_firstname, 
       c.customers_lastname, 
       c.customers_email_address, 
       c.customers_telephone, 
       c.customers_fax, 
       ab.entry_street_address, 
       ab.entry_city, 
       ab.entry_state, 
       ab.entry_country_id, 
       ab.entry_postcode 
FROM   customers c 
       JOIN address_book ab 
         ON c.customers_id = ab.customers_id 
       JOIN customers_info ci 
         ON c.customers_id = ci.customers_info_id 
WHERE  ci.customers_info_date_account_created BETWEEN 
       '2014-10-25' AND '2015-10-10' 

情况 2: 客户已注册并已下订单。此查询从订单表中获取额外的交货信息。以下查询适用于情况 2。

SELECT c.customers_id, 
       c.customers_firstname, 
       c.customers_lastname, 
       c.customers_email_address, 
       c.customers_telephone, 
       c.customers_fax, 
       ab.entry_street_address, 
       ab.entry_city, 
       ab.entry_state, 
       ab.entry_country_id, 
       ab.entry_postcode, 
       o.delivery_street_address, 
       o.delivery_city, 
       o.delivery_state, 
       o.delivery_country, 
       o.delivery_postcode 
FROM   customers c 
       JOIN address_book ab 
         ON c.customers_id = ab.customers_id 
       JOIN orders o 
         ON c.customers_id = o.customers_id 
WHERE  o.date_purchased BETWEEN '2014-10-25' AND '2015-10-10' 

案例 3(我想要实现的目标):我希望使用以下方法让所有客户在特定时间段内注册

ci.customers_info_date_account_在“2014-10-25”和“2015-10-10”之间创建

此外，我想检查客户是否在“2014-10-25”和“2015-10-10”之间使用 o.date_purchased 下订单。如果下订单，则从订单表中获取所有交货信息。如果未下订单，则将这些列留空。

o.delivery_street_address、o.delivery_city、o.delivery_state、o.delivery_country、o.delivery_postcode

Answer 1

    SELECT c.customers_id, 
       c.customers_firstname, 
       c.customers_lastname, 
       c.customers_email_address, 
       c.customers_telephone, 
       c.customers_fax, 
       ab.entry_street_address, 
       ab.entry_city, 
       ab.entry_state, 
       ab.entry_country_id, 
       ab.entry_postcode,
       o.delivery_street_address, o.delivery_city, o.delivery_state, o.delivery_country, o.delivery_postcode  
FROM   customers c 
       INNER JOIN address_book ab 
         ON c.customers_id = ab.customers_id 
       INNER JOIN customers_info ci 
         ON c.customers_id = ci.customers_info_id
       LEFT JOIN (select * from orders where date_purchased BETWEEN '2014-10-25' AND '2015-10-10') o
         ON c.customers_id = o.customers_id 
WHERE  ci.customers_info_date_account_created BETWEEN 
       '2014-10-25' AND '2015-10-10'

您可以在子查询中列出字段名称而不是 *。