从一列中选择最小（日期）、最大（日期）和按天分组 - SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26265481/
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
Select min(date), max(date) and group by day from one column - SQL
提问by m0rte0
I'm going nuts soon please help.
我快疯了，请帮忙。
I have one column containing datetimevalues.
我有一列包含datetime值。
I need to find min and max for every day.
我需要找到每天的最小值和最大值。
The data looks like this
数据看起来像这样
2012-11-23 05:49:26.000
2012-11-23 07:55:43.000
2012-11-23 13:59:56.000
2012-11-26 07:51:13.000
2012-11-26 10:23:31.000
2012-11-26 10:25:09.000
2012-11-26 16:22:22.000
2012-11-27 07:30:03.000
2012-11-27 08:53:47.000
2012-11-27 10:40:55.000
This is what tried so far
这是迄今为止所尝试的
select distinct(convert(nvarchar, datum, 112)), min(datum), max(datum)
from myTable
but when I
但是当我
Group by
I group on all 3 columns...
我对所有 3 列进行分组...
I seems not to work to set my first select as ColName and Group on this
我似乎无法将我的第一个选择设置为 ColName 和 Group
This is what I want
这就是我要的
20121123 | 2012-11-23 05:49:26.000 | 2012-11-23 13:59:56.000
20121126 | 2012-11-26 07:51:13.000 | 2012-11-26 16:22:22.000
20121127 | 2012-11-27 07:30:03.000 | 2012-11-27 10:40:55.000
回答by paqogomez
Convert the column in the GROUP BYas well.
也转换中的列GROUP BY。
select
min(datum), max(datum), CONVERT(varchar(8), datum, 112)
from
dateTable
group by
CONVERT(varchar(8), datum, 112)
Here is a fiddle
这是一个小提琴
Here are the list of convert values for dates. (I've chosen 112 for you in this case above)
以下是日期的转换值列表。（在上面的这种情况下，我为您选择了 112）
回答by Gordon Linoff
In SQL Server 2008+, you can use the datedata type instead of converting to a character string:
在 SQL Server 2008+ 中，您可以使用date数据类型而不是转换为字符串：
select cast(datum as date), min(datum), max(datum)
from myTable
group by cast(datum as date);
