列在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中

问题描述

显然，当使用 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 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！