SQL case 语句中的不等号似乎是向后工作的？
这里是n00b提问者。我正在尝试执行一个查询来检查最近的活动是否在过去 24 小时内。从技术上讲，我可以获得我想要的结果，但我的案例陈述中的不平等必须与我认为有意义的相反方向。这是我的查询：
SELECT sqs.registration_id,
MAX(sqs.completed_at) AS 'most recent activity',
DATE_SUB(NOW(), INTERVAL 1 DAY) AS 'one day ago',
'recent activity?' = CASE
WHEN MAX(sqs.completed_at) <
DATE_SUB(NOW(), INTERVAL 1 DAY)
THEN 1
ELSE 0
END
FROM student_quiz_states sqs
WHERE sqs.score = 100
GROUP BY sqs.registration_id
这是一个示例结果：
XXXXX 2011-08-02 16:23:53 2011-12-05 00:06:05 0
该用户在过去 24 小时内没有活动，因此最后一个值返回 0，正如我希望的那样。
然而，这对我来说没有任何意义。由于第一个日期时间比一天前早得多，所以 case 语句不应该返回 1 吗？如果当我的 when_clause 包含 > 时返回我想要的结果，这对我来说是有意义的。而不是 <。
任何解释将不胜感激。
n00b questioner here. I'm trying to do a query that checks if the most recent activity is within the last 24 hours. I technically can get the result I want, but the inequality in my case statement has to be in the opposite direction as would make sense to me. Here's my query:
SELECT sqs.registration_id,
MAX(sqs.completed_at) AS 'most recent activity',
DATE_SUB(NOW(), INTERVAL 1 DAY) AS 'one day ago',
'recent activity?' = CASE
WHEN MAX(sqs.completed_at) <
DATE_SUB(NOW(), INTERVAL 1 DAY)
THEN 1
ELSE 0
END
FROM student_quiz_states sqs
WHERE sqs.score = 100
GROUP BY sqs.registration_id
Here's an example result:
XXXXX 2011-08-02 16:23:53 2011-12-05 00:06:05 0
This user did not have activity in the last 24 hours, so the last value returns 0, as I want it to.
However, that doesn't make any sense to me. Shouldn't the case statement return a 1, since the first datetime is much earlier than one day ago? It would make sense to me if my desired results were returned when my when_clause contained a > instead of a <.
Any explanations would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题是您的查询包含
“最近的活动？” = CASE ... END其中应该有CASE ... END AS '最近活动？'。前者是相等测试而不是带有别名的表达式。看似“反转”行为的原因是，由于CASE表达式是数字（其计算结果为0或1），MySQL 执行数字相等测试，通过将'recent Activity?'转换为 0.0 (这里有详细规则），例如“最近的活动？” = CASE ... END当CASE表达式给出0时为 true，当CASE表达式给出0时为 false给出1。由于 MySQL 将 true 表示为1，将 false 表示为0，因此最终结果与您的预期相反。（注意：此答案的早期版本，同时对相等测试与别名以及关于 false 和 true 为
0 提出了相同的基本观点code> 和1在其他方面很模糊/混乱，因为我不知道 DBMS 是 MySQL，并且不知道某些 DBMS 允许在引用别名时使用单引号。所以如果上面和下面的一些评论看起来有点奇怪，因为他们指的是那个版本。答案的当前状态很大程度上要归功于这些评论。）The problem is that your query contains
'recent activity?' = CASE ... ENDwhere it should haveCASE ... END AS 'recent activity?'. The former is an equality-test rather than an expression with an alias. The reason for the seemingly "inverted" behavior is that, since theCASEexpression is numeric (it evaluates to0or1), MySQL performs a numeric equality-test, by converting'recent activity?'to 0.0 (detailed rules here), such that'recent activity?' = CASE ... ENDis true when theCASEexpression gives0and false when it gives1. Since MySQL represents true as1and false as0, the end result is the opposite of what you were expecting.(Note: An earlier version of this answer, while making the same basic point about equality-tests vs. aliases, and about false and true being
0and1, was vague/confused in other respects, since I didn't recognize that the DBMS was MySQL, and was not aware that some DBMSes allow single-quotes to be used when quoting aliases. So if some of the comments above and below seem a bit strange, it's because they're referring to that version. The current state of the answer is thanks in large part to those comments.)