SQL SQLite 中 WHERE 子句中的聚合函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/390534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Aggregate functions in WHERE clause in SQLite
提问by Kyle Cronin
Simply put, I have a table with, among other things, a column for timestamps. I want to get the row with the most recent (i.e. greatest value) timestamp. Currently I'm doing this:
简而言之，我有一个表格，其中包含一列时间戳。我想获得具有最新（即最大值）时间戳的行。目前我正在这样做：
SELECT * FROM table ORDER BY timestamp DESC LIMIT 1
But I'd much rather do something like this:
但我更愿意做这样的事情：
SELECT * FROM table WHERE timestamp=max(timestamp)
However, SQLite rejects this query:
但是，SQLite 拒绝此查询：
SQL error: misuse of aggregate function max()
The documentationconfirms this behavior (bottom of page):
该文件证实了这一行为（页面底部）：
Aggregate functions may only be used in a SELECT statement.
聚合函数只能在 SELECT 语句中使用。
My question is: is it possible to write a query to get the row with the greatest timestamp without ordering the select and limiting the number of returned rows to 1? This seems like it should be possible, but I guess my SQL-fu isn't up to snuff.
我的问题是：是否可以编写一个查询来获取具有最大时间戳的行而不对选择进行排序并将返回的行数限制为 1？这似乎应该是可能的，但我想我的 SQL-fu 不符合要求。
回答by SquareCog
SELECT * from foo where timestamp = (select max(timestamp) from foo)
or, if SQLite insists on treating subselects as sets,
或者，如果 SQLite 坚持将子选择视为集合，
SELECT * from foo where timestamp in (select max(timestamp) from foo)
回答by ADP
There are many ways to skin a cat.
有很多方法可以给猫剥皮。
If you have an Identity Column that has an auto-increment functionality, a faster query would result if you return the last record by ID, due to the indexing of the column, unless of course you wish to put an index on the timestamp column.
如果您有一个具有自动递增功能的标识列，由于列的索引，如果您按 ID 返回最后一条记录，则会导致更快的查询，除非您当然希望在时间戳列上放置索引。
SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1
回答by Tom H
I think I've answered this question 5 times in the past week now, but I'm too tired to find a link to one of those right now, so here it is again...
我想我在过去一周里已经回答了这个问题 5 次，但我现在太累了，找不到其中一个的链接，所以又来了……
SELECT
*
FROM
table T1
LEFT OUTER JOIN table T2 ON
T2.timestamp > T1.timestamp
WHERE
T2.timestamp IS NULL
You're basically looking for the row where no other row matches that is later than it.
您基本上是在寻找没有其他行匹配的行。
NOTE: As pointed out in the comments, this method will not perform as well in this kind of situation. It will usually work better (for SQL Server at least) in situations where you want the last row for each customer (as an example).
注意：正如评论中所指出的，这种方法在这种情况下不会表现得很好。在您希望每个客户的最后一行（例如）的情况下，它通常会更好（至少对于 SQL Server）。
回答by ABH
you can simply do
你可以简单地做
SELECT *, max(timestamp) FROM table
SELECT *, max(timestamp) FROM table
Edit:As aggregate function can't be used like this so it gives error. I guess what SquareCoghad suggested was the best thing to do
编辑：由于聚合函数不能像这样使用，所以它会出错。我猜SquareCog 的建议是最好的做法
SELECT * FROM table WHERE timestamp = (select max(timestamp) from table)
