SQL查询:每个GROUP BY表达式都必须包含至少一个不是外部引用的列 [英] SQL Query: 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帐户55未找到
823 2010-09-24 08:00:56.000 9001帐户22222222222找不到
822 2010-09-24 05:06:27.000 9001帐户55找不到
821 2010-09-24 05:05:42.000 9001帐户22222222222找不到
我正在尝试获取errormessage,并且是第一次出现在当前日期,并且这是工作的,因为我只是执行通过ErrorMessage分组。
然而,如果我想每天找到第一个:
SELECT Error(ErrorDateTime)as'ErrorDateTime',Min(ErrorMessage)as'ErrorMessage'
FROM CommonError
WHERE dbo.StripTimeFromDate(ErrorDateTime)= dbo.StripTimeFromDate(getdate())
和ErrorNumber ='9001'
GROUP BY dbo.StripTimeFromDate(getdate()),ErrorMessage
方便的花花公子功能(来自 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
我有getdate()而不是ErrorDate在group by中。
我认为我在其他论坛中看到类似的问题,人们在group by中有字面意思。
更正是:
GROUP BY dbo.StripTimeFromDate(ErrorDate) ,ErrorMessage
Getting this error: Each GROUP BY expression must contain at least one column that is not an outer reference.
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):
ALTER FUNCTION [dbo].[StripTimeFromDate] (@inputDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATEADD(d, DATEDIFF(d, 0, @inputDate), 0)
END
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".
Correction is:
GROUP BY dbo.StripTimeFromDate(ErrorDate), ErrorMessage
这篇关于SQL查询:每个GROUP BY表达式都必须包含至少一个不是外部引用的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!