列在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中 [英] Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

问题描述

显然，当使用 GROUP BY 子句时，不是聚合函数的列应该是 group by 子句的一部分.这里的问题是，我不能包含 HTTPADDRESS &GROUP BY 子句中的 DATEENTERED 列.另外，我不知道可以为我提供所有最新条目的功能.

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.

我使用 sql-server.如果我使用 access，我会使用 LAST 函数.

edit: I use sql-server. I would use LAST function if I were using access.

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)    

推荐答案

你必须自己加入回来:

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

注意，这假定给定的 VISITORIP 将在该范围内具有唯一的最大 DATEENTERED.

Note, this assumes a given VISITORIP will have a unique maximum DATEENTERED in the range.

这篇关于列在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！