MySQL实战-拍卖展示规则

foreclosure 表

auction_date：拍卖时间 date

可能状态

• 已经结束： 小于当前时间
• 正在进行： 等于当前时间
• 即将开始： 大于当前时间

排序规则

正在进行的放最前边；

之后是即将开始的，越靠近今天越靠前 -> auction_date 与 CURDATE() 越接近越靠前 -> 越小越靠前 -> 升序；

最后是已经结束的，越靠近今天越靠前 -> auction_date 与 CURDATE() 越接近越靠前 -> 越大越靠前 -> 降序；

语法1：待测试

SELECT *
FROM `foreclosure`
ORDER BY
CASE
WHEN auction_time >= CURDATE() THEN
— 对于今日及未来的拍卖，值越小的拍卖越靠前
DATEDIFF(auction_time, CURDATE())
ELSE
— 对于过期的拍卖，值越小的拍卖越靠前
DATEDIFF(CURDATE(), auction_time)
END;

注意事项

• 在排序时，今日及未来的拍卖和过期的拍卖是按时间差进行排序的，因此，可能会混合显示不同类别的拍卖。
• 如果 auction_time 为 NULL，DATEDIFF 函数会返回 NULL。
• 在排序中，NULL 值在升序排序时被认为是最大的，所以它们会被排在最后。

auction_time：拍卖时间 时间戳

语法2：（实战有bug）

SELECT *
FROM `foreclosure`
ORDER BY
CASE
    WHEN auction_time >= UNIX_TIMESTAMP(CURDATE()) THEN
        -- 对于今日及未来的拍卖，按拍卖时间升序排序
        auction_time
    ELSE
        -- 对于过期的拍卖，按拍卖时间降序排序
        UNIX_TIMESTAMP(CURDATE()) - auction_time
END;

执行上述语句后：若是2024-08-09，2024-08-12排前边，接着是2024-08-01，接着是2024-08-18，已经过期的混入到即将开始之前。

语句3：（测试正确）

我们需要一个明确的排序规则来确保：

1. 今日及未来的拍卖按升序排列（即从最接近今日的拍卖开始）。
2. 过期的拍卖按降序排列（即从最接近今日的过期拍卖开始）。

SELECT *
FROM `foreclosure`
ORDER BY
-- 先处理今日及未来的拍卖
 CASE
    WHEN auction_time > UNIX_TIMESTAMP(CURDATE()) THEN
        -- 对于今日及未来的拍卖，按拍卖时间升序排序
        auction_time
    ELSE
        -- 对于过期的拍卖，将其设为最大值以排在后面
        9223372036854775807
END,
CASE
    WHEN auction_time < UNIX_TIMESTAMP(CURDATE()) THEN
        -- 过期拍卖按时间降序排列
        UNIX_TIMESTAMP(CURDATE()) - auction_time
    ELSE
        -- 对于NULL，将其设为最小值以排在后面
        0
END;

注意：UNIX_TIMESTAMP(CURDATE()) 获取的是当天的 00:00:00 时间戳，这就导致在12:00查看数据时，今日10:00的数据本来已经过期了，但还是排在最前边

可以使用 UNIX_TIMESTAMP(NOW()) 来替换 UNIX_TIMESTAMP(CURDATE())。NOW() 函数会返回当前的日期和时间。

SELECT *
FROM `foreclosure`
ORDER BY
-- 先处理今日及未来的拍卖
 CASE
    WHEN auction_time > UNIX_TIMESTAMP(NOW()) THEN
        -- 对于今日及未来的拍卖，按拍卖时间升序排序
        auction_time
    ELSE
        -- 对于过期的拍卖，将其设为最大值以排在后面
        9223372036854775807
END,
CASE
    WHEN auction_time < UNIX_TIMESTAMP(NOW()) THEN
        -- 过期拍卖按时间降序排列
        UNIX_TIMESTAMP(NOW()) - auction_time
    ELSE
        -- 对于NULL，将其设为最小值以排在后面
        0
END;