如何筛选并删除近5年无订单的用户？SQL查询优化求助

你的当前查询存在两个核心问题：

1. 使用innerJoin会自动排除从未下过单的用户，但这类用户也属于“近5年未下单”的范畴，应该被纳入删除范围。
2. 直接通过orderTime < (:date)筛选订单行再分组，会误包含那些既有旧订单又有新订单的用户——只要用户有一条旧订单满足条件，就会被选中，但实际上他们有近5年内的订单，不能删除。

要准确筛选出符合要求的用户，需要确保检查用户的所有订单，判断其最后一次订单时间是否早于5年前，或者从未有过订单。以下是两种可行的实现方案：

方案一：通过LEFT JOIN + 聚合函数筛选 #

利用LEFT JOIN保留所有用户，再通过MAX(sOrder.ordertime)获取每个用户的最后下单时间，最终筛选出符合条件的用户：

$builder = $this->connection->createQueryBuilder();

$builder->select('sUser.id as userId')
    ->from('s_user', 'sUser')
    // LEFT JOIN保留所有用户，即使没有订单
    ->leftJoin('sUser', 's_order', 'sOrder', 'sUser.id = sOrder.userID')
    ->where('sUser.accountmode = (:accountmode)')
    ->groupBy('sUser.id')
    // 核心筛选逻辑：要么无订单，要么最后一次下单早于指定日期
    ->having('MAX(sOrder.ordertime) < (:date) OR MAX(sOrder.ordertime) IS NULL')
    ->setParameter(':accountmode', 1, \PDO::PARAM_INT)
    ->setParameter(
        ':date',
        date('Y-m-d', strtotime("-$this->yearsAfterLastOrder years")),
        \PDO::PARAM_STR
    );

如果要执行删除操作，可以基于这个查询的结果构建删除语句：

$deleteBuilder = $this->connection->createQueryBuilder();
$deleteBuilder->delete('s_user')
    ->where('id IN (' . $builder->getSQL() . ')')
    ->setParameters($builder->getParameters());

// 执行删除
$deleteBuilder->execute();

方案二：通过子查询排除近5年有订单的用户 #

先查询出近5年内有过订单的用户ID，再从目标用户中排除这些ID，逻辑更直观，在订单表数据量较大时性能可能更优：

// 子查询：获取近5年有订单的用户ID
$hasRecentOrderSubQuery = $this->connection->createQueryBuilder()
    ->select('userID')
    ->from('s_order')
    ->where('ordertime >= (:date)');

// 主查询：筛选出符合条件的用户
$builder = $this->connection->createQueryBuilder();
$builder->select('id as userId')
    ->from('s_user')
    ->where('accountmode = (:accountmode)')
    ->andWhere('id NOT IN (' . $hasRecentOrderSubQuery->getSQL() . ')')
    ->setParameter(':accountmode', 1, \PDO::PARAM_INT)
    ->setParameter(
        ':date',
        date('Y-m-d', strtotime("-$this->yearsAfterLastOrder years")),
        \PDO::PARAM_STR
    );

关键说明 #

• 两种方案都覆盖了“从未下单”和“最后一次下单早于5年前”两类用户。
• 方案一适合需要同时查看用户最后下单时间的场景；方案二更侧重高效筛选，适合仅需用户ID执行删除的场景。

内容的提问来源于stack exchange，提问作者Hürkan Doğan