将日期分类为“昨天”，“上周”等-Classifyingdateas“yesterday”,“lastweek”,etc

3  

Presumably, the logic that you want is like this:

据推测，你想要的逻辑是这样的：

SELECT id,
       (CASE WHEN FROM_UNIXTIME(date_time) >= CURDATE() THEN 'today'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 'yesteray'
             WHEN FROM_UNIXTIME(date_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'in last week'
             ELSE 'in last month or more'
        END) as `range`
FROM mytable
WHERE 1;

Notes:

笔记：

• There is no reason to extract the date.
• 没有理由提取日期。
• Clauses in a case statement are executed in sequence, so the first matching one will return a value.
• case语句中的子句按顺序执行，因此第一个匹配的子句将返回一个值。
• If your original code is failing, then this might fail as well. Your original code should not have been returning only "yesterday".
• 如果您的原始代码失败，那么这也可能会失败。您的原始代码不应该只返回“昨天”。
• range is a reserved word, so it needs to be escaped.
• range是保留字，因此需要进行转义。

Here is the SQL Fiddle.

这是SQL小提琴。

2  

The issue is you arent checking a range for the week on your third condition. Meaning...

问题是你没有在第三个条件下检查一周的范围。含义...

DATE(FROM_UNIXTIME(date_time)) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY

FIDDLE

小提琴

Currently your code is saying WHEN CURDATE() - INTERVAL 7 DAY THEN 'in last week' if the date is equal to 7 days ago ONLY. the 25th is not the 22nd so it fails that check. You need to specify a range in order to get it to work.

目前你的代码正在说什么CURDATE（） - 如果日期等于7天前，那么在上周的“间隔7天那天”。 25日不是第22次所以它没有通过检查。您需要指定一个范围才能使其正常工作。