（使用join）我没有包含在聚合函数或GROUP BY错误中

I get the following error.

我收到以下错误。

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'STATS.VisitorIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]列'STATS.VisitorIP'在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。

I used VISITORIP column in both select lists. I used GROUP BY clause in one of them so I addded VISITORIP to it. But I dont have GROUP BY clause in other select so did not add it. Why do I receive this error?

我在两个选择列表中都使用了VISITORIP列。我在其中一个中使用了GROUP BY子句,所以我给它添加了VISITORIP。但是我没有在其他选择中使用GROUP BY子句,所以没有添加它。为什么我收到此错误?

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "') S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

4 个解决方案

#1

You need the GROUP BY in the nested subquery because you are trying to get the MAX DATEENTERED for each VISITORIP. VISITORIP exists in the SELECT for that subquery, so you need the GROUP BY in there also in order for it to return the latest date for each visitorip.

您需要嵌套子查询中的GROUP BY,因为您正在尝试为每个VISITORIP获取MAX DATEENTERED。 VISITORIP存在于该子查询的SELECT中,因此您还需要GROUP BY,以便它返回每个visitorip的最新日期。

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "' GROUP BY S1.VISITORIP) S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

#2

This (nested query) is not valid:

此(嵌套查询)无效:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED)
FROM STATS S1 
WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'

It should be:

它应该是:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) AS DATEENTERED
FROM STATS S1 
WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'
GROUP BY S1.VISITORIP

#3

Your Inner select uses MAX on DATEENTERED but does not include a groupby on the VisitorIP.

您的内部选择在DATEENTERED上使用MAX,但在VisitorIP上不包含groupby。

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED)
   FROM STATS S1
   WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'
   GROUPBY S1.VISITORIP) S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

#4

You're getting the error because your subselect in the JOIN:

您收到错误是因为您在JOIN中的子选择:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'

Is attempting to aggregate "DATEENTERED", but needs to group on "VISITORIP" in order to do so.

试图聚合“DATEENTERED”,但需要在“VISITORIP”上进行分组才能这样做。

Try:

SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "' GROUP BY S1.VISITORIP

#1

SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
FROM STATS S
JOIN (SELECT S1.VISITORIP, MAX(S1.DATEENTERED) FROM STATS S1 WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "' GROUP BY S1.VISITORIP) S1
ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED
WHERE S.DATEENTERED BETWEEN '" &TIME& "' AND '" & NOW() & "'
GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED
ORDER BY S.DATEENTERED DESC

#2