SQL 查询：每个 GROUP BY 表达式必须包含至少一列不是外部引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3789487/
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
SQL Query: Each GROUP BY expression must contain at least one column that is not an outer reference
提问by NealWalters
Getting this error: Each GROUP BY expression must contain at least one column that is not an outer reference.
得到这个错误：每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。
RowId ErrorDatetime ErrorNum ErrorMessage
824 2010-09-24 08:01:42.000 9001 Account 55 not found
823 2010-09-24 08:00:56.000 9001 Account 22222222222 not found
822 2010-09-24 05:06:27.000 9001 Account 55 not found
821 2010-09-24 05:05:42.000 9001 Account 22222222222 not found
I'm trying to get the errormessage, and the first time it occurred for the current day, and that's working because I just do "Group by ErrorMessage".
我正在尝试获取错误消息，并且它是当天第一次发生的，这是有效的，因为我只是执行“按错误消息分组”。
However, if I want to find the first for each day:
但是，如果我想找到每天的第一个：
SELECT Min(ErrorDateTime) as 'ErrorDateTime', Min(ErrorMessage) as 'ErrorMessage'
FROM CommonError
WHERE dbo.StripTimeFromDate(ErrorDateTime) = dbo.StripTimeFromDate(getdate())
and ErrorNumber = '9001'
GROUP BY dbo.StripTimeFromDate(getdate()), ErrorMessage
The handy-dandy function (from http://bloggingabout.net/blogs/jschreuder/archive/2007/03/13/useful-t-sql-date-functions.aspx):
方便花花公子的功能（来自http://bloggingabout.net/blogs/jschreuder/archive/2007/03/13/useful-t-sql-date-functions.aspx）：
ALTER FUNCTION [dbo].[StripTimeFromDate] (@inputDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATEADD(d, DATEDIFF(d, 0, @inputDate), 0)
END
回答by Joe Stefanelli
First, you don't need the MIN(ErrorMessage) in your SELECT. Simply SELECT ErrorMessage. Second, GROUP BY ErrorMessage only.
首先，您的 SELECT 中不需要 MIN(ErrorMessage)。只需选择错误消息。其次，仅 GROUP BY ErrorMessage。
SELECT Min(ErrorDateTime) as 'ErrorDateTime', ErrorMessage
FROM CommonError
WHERE dbo.StripTimeFromDate(ErrorDateTime) = dbo.StripTimeFromDate(getdate())
and ErrorNumber = '9001'
GROUP BY ErrorMessage
回答by OMG Ponies
Use:
用：
SELECT t.errormessage, MIN(t.errordatetime) AS dt
FROM COMMONERROR t
GROUP BY t.errormessage, DATEADD(d, DATEDIFF(d, 0, t.errordatetime), 0)
Check the references in the GROUP BY; it wasn't getting caught because MIN was being applied to the same column.
检查 GROUP BY 中的引用；它没有被捕获，因为 MIN 被应用于同一列。
Assuming SQL Server 2005+, this is easier to handle with analytics:
假设 SQL Server 2005+，这更容易通过分析处理：
WITH base AS (
SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY t.errormessage, DATEADD(d, DATEDIFF(d, 0, t.errordatetime), 0)
ORDER BY t.errordatetime) AS rk
FROM dbo.COMMONERROR t)
SELECT b.*
FROM base b
WHERE b.rk = 1
AND b.errornumber = '9001'
AND DATEADD(d, DATEDIFF(d, 0, b.errordatetime), 0) = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
回答by NealWalters
I had getdate() instead of ErrorDate in the "group by". I think I saw similar issues in other forums where people had a literal in the "group by".
我在“group by”中有 getdate() 而不是 ErrorDate 。我想我在其他论坛上看到了类似的问题，人们在“group by”中有文字。
Correction is:
更正是：
GROUP BY dbo.StripTimeFromDate(ErrorDate), ErrorMessage
