mysql - 列出过去 12 个月内未订购的 Magento 客户

Question

在 Magento v1.9 中，我需要一个 MYSQL select 来执行以下操作:

• 列出过去 12 个月内未下任何订单的所有客户

谢谢!

Answer 1

基本上，您需要从未下过订单的客户或上次下单时间超过 12 个月的客户。您可以使用客户集合来做到这一点:

$date = new DateTime();
$date->sub(new DateInterval('P12M'));
$customers = Mage::getModel('customer/customer')->getCollection();
$customers->getSelect()->joinLeft(
    array('o' => Mage::getSingleton('core/resource')->getTableName('sales/order')),
    'o.customer_id = e.entity_id',
    array(
        'last_order_date' => 'MAX(o.created_at)',
    )
);
$customers->groupByAttribute('entity_id')
    ->getSelect()
    ->having('last_order_date < ?',$date->format('Y-m-d'))
    ->orHaving('last_order_date IS NULL');

或者如果你想要 mysql 只调用$customers->getSelect();

这将为您提供以下 MYSQL 查询

SELECT `e`.*, MAX(o.created_at) AS `last_order_date` FROM `customer_entity` AS `e` LEFT JOIN `sales_flat_order` AS `o` ON o.customer_id = e.entity_id WHERE (`e`.`entity_type_id` = '1') GROUP BY `e`.`entity_id` HAVING (last_order_date < '2014-03-26') OR (last_order_date IS NULL)

希望对您有所帮助!