如何避免MySQL查询结果出现负号？REPLACE()方法无效求助

你的查询中出现负号的核心原因是MySQL的DATEDIFF()函数参数顺序：DATEDIFF(date1, date2)返回的是date1 - date2的天数差。你当前的写法是用「更早的日期」减去「当前start_date」，结果自然为负。

另外你尝试用REPLACE()无效，是因为DATEDIFF()返回的是数值类型，而REPLACE()仅对字符串生效，对数值的负号不起作用。

有两种简单的解决方式：

方式1：调整DATEDIFF参数顺序 #

把当前日期放在第一个参数，更早的日期放在第二个，这样得到的就是正数：

WITH Partitioned AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, start_date ORDER BY customer_id, start_date) AS RowNumber
    FROM data_bank.customer_nodes
) 
SELECT 
    *,
    CASE 
        WHEN RowNumber > 1 THEN 0
        ELSE COALESCE(DATEDIFF(a.start_date, (SELECT MAX(start_date) FROM data_bank.customer_nodes WHERE start_date < a.start_date AND customer_id = a.customer_id)), 0)
    END AS Days_between
FROM Partitioned a 

方式2：用ABS()取绝对值 #

如果不想调整参数顺序，直接对DATEDIFF的结果取绝对值，确保结果非负：

WITH Partitioned AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, start_date ORDER BY customer_id, start_date) AS RowNumber
    FROM data_bank.customer_nodes
) 
SELECT 
    *,
    CASE 
        WHEN RowNumber > 1 THEN 0
        ELSE COALESCE(ABS(DATEDIFF((SELECT MAX(start_date) FROM data_bank.customer_nodes WHERE start_date < a.start_date AND customer_id = a.customer_id), a.start_date)), 0)
    END AS Days_between
FROM Partitioned a 

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