SQL查询获取每位客户的第100笔订单下单日期

现有订单表order，存储客户下单数据，字段包含客户标识customer、订单IDorder_id、下单日期order_date，样例数据如下：

需要编写SQL实现：为每个客户返回一行结果，展示其第100笔订单的下单日期，字段命名为100_order_date。订单数不足100笔的客户，可选择返回null值，或直接过滤不展示该客户行。

核心逻辑是先对每个客户的订单按下单时序排序编号，再筛选编号为100的记录即可。注意：订单排序规则优先按order_date升序，同日期下加order_id升序兜底，避免单日多笔订单导致计数错位。

方案1：窗口函数实现（推荐，适配MySQL8.0+、PostgreSQL、SQL Server、Hive等主流数据库） #

版本1：不足100笔的客户保留、返回null #

WITH order_with_rn AS (
    SELECT
        customer,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date, order_id) AS rn
    FROM `order`
)
SELECT
    base.customer,
    target.order_date AS `100_order_date`
FROM (SELECT DISTINCT customer FROM `order`) base
LEFT JOIN order_with_rn target
    ON base.customer = target.customer
    AND target.rn = 100;

版本2：直接过滤不足100笔的客户 #

WITH order_with_rn AS (
    SELECT
        customer,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date, order_id) AS rn
    FROM `order`
)
SELECT
    customer,
    order_date AS `100_order_date`
FROM order_with_rn
WHERE rn = 100;

方案2：低版本MySQL兼容（不支持窗口函数的MySQL5.x场景） #

通过关联子查询计数实现排序效果，会自动过滤订单数不足100的客户：

SELECT
    o1.customer,
    o1.order_date AS `100_order_date`
FROM `order` o1
WHERE (
    SELECT COUNT(*)
    FROM `order` o2
    WHERE o2.customer = o1.customer
      AND (
          o2.order_date < o1.order_date
          OR (o2.order_date = o1.order_date AND o2.order_id <= o1.order_id)
      )
) = 100;

内容的提问来源于stack exchange，提问作者Audrey Z