因为它不是在聚合函数或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 DATEENTERED列BY子句。另外，我不知道一个函数，会给我所有的最新作品。

编辑：我使用的SQL服务器。如果我是使用访问我会用最后一个函数。

  SQL =SELECT VISITORIP，HTTPADDRESS，DATEENTERED
SQL = SQL＆安培; FROM STATS
SQL = SQL＆安培; WHE​​RE DATEENTERED BETWEEN'＆放大器; OnlineTime＆安培; '和'＆放大器; NOW（）及'
SQL = SQL＆安培; GROUP BY VISITORIP
SQL = SQL＆安培; ORDER BY DATEENTERED DESC
设置objOnVisitors = objConn.Execute（SQL）
 

解决方案

您必须自联接回：

  WITH AS LASTVISIT（
    SELECT VISITORIP，MAX（DATEENTERED）AS DATEENTERED
    FROM STATS
    WHERE DATEENTERED BETWEEN @STARTTIME和@ENDTIME
    GROUP BY VISITORIP
）
SELECT STATS.VISITORIP，STATS.HTTPADDRESS，STATS.DATEENTERED
FROM STATS
INNER JOIN LASTVISIT
    ON LASTVISIT.VISITORIP = STATS.VISITORIP
    和LASTVISIT.DATEENTERED = STATS.DATEENTERED
ORDER BY STATS.DATEENTERED DESC
 

请注意，这个假设给定VISITORIP将在范围的唯一最大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.

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)

解决方案

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.

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