如何在SQL中按列分组？

How can I group by Time? I tried this, but it gives the error "Invalid column name 'Time'.":

我怎样才能按时间分组?我尝试了这个,但它给出了错误“无效的列名'时间'。”:

select Count(Page) as VisitingCount, CONVERT(VARCHAR(5), Date, 108) as [Time]
from scr_SecuristLog   
where Date between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in (select USERNAME from scr_CustomerAuthorities)  
group by [Time] order by [VisitingCount] asc

5 个解决方案

#1

[Time] is a column alias. Try

[时间]是列别名。尝试

SELECT 
      COUNT(Page) AS VisitingCount
    , CONVERT(VARCHAR(5),Date, 108) AS [Time] 
FROM
    scr_SecuristLog   
WHERE
    Date BETWEEN '2009-05-04 00:00:00' AND '2009-05-06 14:58'  
    AND
    [user] IN (
                SELECT 
                    USERNAME             
                FROM
                    scr_CustomerAuthorities 
                )  
GROUP BY
    CONVERT(VARCHAR(5),Date, 108) 
ORDER BY
    [VisitingCount] ASC

#2

Try

GROUP BY CONVERT(VARCHAR(5),Date, 108)

Always make sure you group by everything in your select clause that does not have an aggregate function around it.

始终确保按照select子句中没有聚合函数的所有内容进行分组。

#3

select Count(Page) as VisitingCount,CONVERT(VARCHAR(5),Date, 108) as [Time] from scr_SecuristLog   
where Date between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by CONVERT(VARCHAR(5),Date, 108) order by [VisitingCount] asc

I modified the GROUP BY to include the actual expression for [Time], rather than the column alias (since this can't be used in the GROUP BY, only the ORDER BY)

我修改了GROUP BY以包含[Time]的实际表达式,而不是列别名(因为这不能在GROUP BY中使用,只能在ORDER BY中使用)

#4

It looks like Date is a column here, but it's a keyword and not quoted. Perhaps that's the issue (not tested though):

看起来Date在这里是一列,但它是一个关键字而没有引用。也许这就是问题(虽然未经过测试):

select Count(Page) as VisitingCount,CONVERT(VARCHAR(5),[Date], 108) as [Time] from scr_SecuristLog   
where [Date] between '2009-05-04 00:00:00' and '2009-05-06 14:58'  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by [Time] order by [VisitingCount] asc

#5

If you don't want to repeat the date conversion (Sometimes calculation is a bit more intensive than simple conversion), then you can use something like this:

如果您不想重复日期转换(有时计算比简单转换更加密集),那么您可以使用以下内容:

select * 
from ( select Count(page) .., ... As [Date] from ... where ...) UG
group by UG.[Date]

Notice that you have to give the 'UG' name to internal select and that this will be most likely and in most cases less efficient than repeating the conversion expression in original grouping. Also your whole expression is likely to change... But so just you know that it is possible.

请注意,您必须将“UG”名称赋予内部选择,并且这很可能并且在大多数情况下比在原始分组中重复转换表达式效率低。你的整个表达也可能会改变......但是你知道这是可能的。

#1