这个 MySQL 查询有什么隐藏的陷阱吗？
我希望这不是一个非常迟钝的问题。我注意到 MySQL 会让我引用 GROUP 和 ORDER 中的“Mnth”字段。并非我使用过的每个数据库引擎都是这种情况。任何人都可以确认这是一个好的方法吗？
SELECT DATE_FORMAT(FROM_UNIXTIME(`swauditlogs`.`dateline`),'%Y-%m') AS Mnth, Count(`swauditlogs`.`ticketid`) AS Count
FROM swauditlogs LEFT JOIN swtickets ON swauditlogs.ticketid = swtickets.ticketid
WHERE swauditlogs.actionmsg Like 'Ticket status changed from:%to: Closed'
GROUP BY Mnth
ORDER BY Mnth DESC
I hope this isn't a terribly obtuse question. I notice that MySQL will let me refer back to the "Mnth" field in my GROUP and ORDER. This is not the case in every db engine I've worked with. Can anyone confim that this is an OK approach?
SELECT DATE_FORMAT(FROM_UNIXTIME(`swauditlogs`.`dateline`),'%Y-%m') AS Mnth, Count(`swauditlogs`.`ticketid`) AS Count
FROM swauditlogs LEFT JOIN swtickets ON swauditlogs.ticketid = swtickets.ticketid
WHERE swauditlogs.actionmsg Like 'Ticket status changed from:%to: Closed'
GROUP BY Mnth
ORDER BY Mnth DESC
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
没关系。来自 http://dev.mysql.com/doc/refman/5.0 /en/select.html：
不过我不喜欢那个 LIKE 子句。是否没有另一列可以测试，而不必对字符串执行 LIKE？
It's OK. From http://dev.mysql.com/doc/refman/5.0/en/select.html:
I don't like that LIKE clause though. Isn't there another column you can test instead of having to perform a LIKE on a string?
这在 MySql 中是可以接受的：
请参见此处： http: //dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html
...意味着您可以在 MySql 中使用表达式。
That's acceptable in MySql:
see here: http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html
...implying that you can use expressions with MySql.
我不知道任何 MySQL 内部结构，但我在我们的生产系统中使用了类似的查询，直到今天该查询还没有使其崩溃。所以这不是 MySQL 核心黑客的答案，只是我的观察。
哦，我忘了：免责声明：仅仅因为我说我没有看到任何问题，并不意味着没有问题；-)
I don't know about any MySQL internals, but I used a similar query in our production system, and until today the query hasn't crashed it yet. So this is no MySQL core hacker answer, just my observation.
Oh, I forgot: WARRANTY DISCLAIMER: Just because I say that I see no gotchas, doesn't mean there are none ;-)
恕我直言，该查询对于 MySQL 来说看起来没问题。
标准 SQL 不允许在 WHERE 子句中引用列别名，但您可以在 GROUP BY、ORDER BY 或 HAVING 子句中使用别名来引用该列。
http://dev.mysql.com/doc/ refman/5.0/en/problems-with-alias.html
IMHO, the query looks okay for MySQL.
Standard SQL disallows references to column aliases in a WHERE clause but you can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html