mysql - 如果不为零，则选择最高日期

Question

我有下表:

---

邮件

• id(int 11 自动递增)
• 用户 ID(整数 11)
• 主题(varchar 255)
• 正文(正文)
• 已删除(日期时间)

---

默认情况下，deleted 列的值为 0000-00-00 00:00:00 当某些内容被删除时，deleted 列将设置为删除日期。例如:2017-08-14 09:37:13

现在我想查询此表并获取 user_id 记录，其中 deleted = 0000-00-00 00:00:00或者最近的 deleted 值，如果没有用户记录，其中 deleted = 0000-00-00 00:00:00

Answer 1

您可以使用大小写对特定值进行排序:

select  user_id
from    YourTable
order by
        case
        when deleted = '0000-00-00 00:00:00' then '9999-12-31 23:59:59'
        else deleted
        end desc
limit   1

其中 '9999-12-31 23:59:59' 是 maximum value of a datetime .

对于多个用户，您可以通过连接查找最大值:

select  *
from    Mails m1
join    (
        select  user_id
        ,       max(
                case
                when deleted = '0000-00-00 00:00:00' then '9999-12-31 23:59:59'
                else deleted
                end) max_deleted
        from    Mails
        group by
                user_ud
        ) m2
on      m1.user_id = m2.user_id
        and case
            when m1.deleted = '0000-00-00 00:00:00' then '9999-12-31 23:59:59'
            else m1.deleted
            end = m2.max_deleted