SQL 列在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1114048/
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
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
提问by Cade Roux
Clearly, when GROUP BY clause used, columns that are not aggregate function should be part of the group by clause. The problem here is, I cannot contain HTTPADDRESS & DATEENTERED columns in GROUP BY clause. Also, I dont know a function that will give me the latest entries of all.
显然，当使用 GROUP BY 子句时，不是聚合函数的列应该是 group by 子句的一部分。这里的问题是，我不能在 GROUP BY 子句中包含 HTTPADDRESS 和 DATEENTERED 列。另外，我不知道一个可以给我所有最新条目的功能。
edit: I use sql-server. I would use LAST function if I were using access.
编辑：我使用 sql-server。如果我使用访问，我会使用 LAST 函数。
SQL = "SELECT VISITORIP, HTTPADDRESS, DATEENTERED"
SQL = SQL & " FROM STATS"
SQL = SQL & " WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'"
SQL = SQL & " GROUP BY VISITORIP"
SQL = SQL & " ORDER BY DATEENTERED DESC"
Set objOnVisitors = objConn.Execute(SQL)
回答by Cade Roux
You have to self-join back:
您必须自行加入：
WITH LASTVISIT AS (
SELECT VISITORIP, MAX(DATEENTERED) AS DATEENTERED
FROM STATS
WHERE DATEENTERED BETWEEN @STARTTIME AND @ENDTIME
GROUP BY VISITORIP
)
SELECT STATS.VISITORIP, STATS.HTTPADDRESS, STATS.DATEENTERED
FROM STATS
INNER JOIN LASTVISIT
ON LASTVISIT.VISITORIP = STATS.VISITORIP
AND LASTVISIT.DATEENTERED = STATS.DATEENTERED
ORDER BY STATS.DATEENTERED DESC
Note, this assumes a given VISITORIP will have a unique maximum DATEENTERED in the range.
请注意，这假设给定的 VISITORIP 将在范围内具有唯一的最大 DATEENTERED。
