如何将SQL表中同客户的Order_Date统一更新为该客户最早订单日期

假设你的表名为customer_orders，以下是不同数据库环境下的可行实现方案：

方案1：通用自连接更新写法（兼容MySQL、SQL Server等绝大多数数据库） #

修正自连接逻辑的版本，先聚合得到每个客户的最早订单日期，再关联执行更新：

UPDATE customer_orders t1
JOIN (
    SELECT CUSTOMER_ID, MIN(Order_Date) AS first_order_date
    FROM customer_orders
    GROUP BY CUSTOMER_ID
) t2 ON t1.CUSTOMER_ID = t2.CUSTOMER_ID
SET t1.Order_Date = t2.first_order_date;

方案2：PostgreSQL/Oracle 适配写法 #

UPDATE customer_orders t1
SET Order_Date = t2.first_order_date
FROM (
    SELECT CUSTOMER_ID, MIN(Order_Date) AS first_order_date
    FROM customer_orders
    GROUP BY CUSTOMER_ID
) t2
WHERE t1.CUSTOMER_ID = t2.CUSTOMER_ID;

方案3：窗口函数写法（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库） #

逻辑更直观，可读性更强：

WITH customer_first_order AS (
    SELECT 
        CUSTOMER_ID,
        Order_Date,
        MIN(Order_Date) OVER (PARTITION BY CUSTOMER_ID) AS first_order_date
    FROM customer_orders
)
UPDATE customer_first_order
SET Order_Date = first_order_date;

注意：请将上述SQL中的customer_orders替换为你实际使用的表名

结果验证 #

更新完成后执行以下语句即可确认是否符合预期：

SELECT CUSTOMER_ID, Order_Date FROM customer_orders ORDER BY CUSTOMER_ID;

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