按照年月日时分秒分组查询语句的分析

表结构
id          num         date
----------- ----------- -----------------------
1           141313      2010-02-01 00:00:00.000
134         6477        2010-02-01 01:00:00.000
135         7863        2010-02-01 02:00:00.000
136         4317        2010-02-01 03:00:00.000

要查询 num 的 平均值 以时间的年、月、日、时、分、秒 分组!

 

实现之前 先 看一下几个方法！

方法一
datepart
语法
datepart（year，date或者可以放缩写 注：date为 表的列，或变量！就是要取他的某部分！

日期部分的缩写列表
日期部分 缩写
year  yy, yyyy
quarter  qq, q
month  mm, m
dayofyear dy, y
day  dd, d
week  wk, ww
weekday  dw
Hour  hh
minute  mi, n
second  ss, s
millisecond ms

用法
select top 1 datepart(yy,[date]) from dbs
将得到 2010
select top 1 datepart(mm,[date]) from dbs
将得到01（02，03，。。。12）
select top 1 datepart(dd,[date]) from dbs
将得到01（02，03。。。31）
所以假如说按照月查
select datepart(mm,[date]),avg(num) from dbs  group by datepart(mm,[date])
结果为
----------- -----------
1           84593
2           6493
3           14493
4           3433
5           2493
6           4493
7           1793
8           7393
9           8793
10           4483
11           3393
12           4193

看似正确，其实不然，这样的话，不管是多少年的都会被分为12组！（最大12组）
而我们得实现 精度（单位） 最小 为 月的 平均值！意思就是最小精确到月不能把年份丢失！
所以就不行！
但是还是可以实现的！datepart返回值都是int，咱们可以通过转换类型和字符串的拼接来实现，但是

这样的话，就 不划算！因为还得判断返回值是否>10,如果小于10还得给前边加0，时分秒更是麻烦！

所以放弃这种方法！

方法二

mssql自带了几个函数year（），month（），day（）
语法
year（[date]），month（[date]），day（[date]）

但是没有给得到 时分秒毫秒的方法！
这些函数返回值 依然 和上边 相同都是 int
如此说来，若是按照月份分组查询的话，肯定也会最多只有12组！
实现是可以的，但是得拼接字符串，和上边一样麻烦，而且 没有时分秒的函数！也不够用！
所以继续放弃这种方法！

方法三

直接运用convert方法！

语法
CONVERT的使用方法:

　　格式:

　　CONVERT(data_type,expression[,style])

　　说明:

　　此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)

　　相互转换的时候才用到.

　　例子:

　　Select CONVERT(varchar(30),getdate(),101) now

　　结果为

　　now

　　---------------------------------------

　　09/15/2001

　　///

　　style数字在转换时间时的含义如下

　　---------------------------------------------------------------

　　Style(2位表示年份) 　 Style(4位表示年份) 　 输入输出格式

　　---------------------------------------------------------------

　　- 　 0 or 100 　 mon dd yyyy hh:miAM(或PM)

　　---------------------------------------------------------------

　　1 　 101 　 mm/dd/yy

　　----------------------------------------------------------------

　　-----------------------------

　　2 　 102 　 yy-mm-dd

　　------------------------------------------------------------------

　　3 　 103 　 dd/mm/yy

　　-------------

　　-------------------------------------------------------------------

　　4 　 104 　 dd-mm-yy

　　-------------------------------------------------------------------

　　5 　 105 　 dd-mm-yy

　　-------------------------------------------------------------------

　　6 　 106 　 dd mon yy

　　-------------------------------------------------------------------

　　7 　 107 　 mon dd,yy

　　----------------------------------------------------------------------

　　8 　 108 　 hh:mm:ss

　　----------------------------------------------------------------------

　　- 　 9 or 109 　 mon dd yyyy hh:mi:ss:mmmmAM(或PM)

　　----------------------------------------------------------------------

　　10 　 110 　 mm-dd-yy

　　-----------------------------------------------------------------------

　　11 　 111 　 yy/mm/dd

　　----------------------------------------------------------------------

　　12 　 112 　 yymmdd

　　-----------------------------------------------------------------------

　　- 　 13 or 113 　 dd mon yyyy hh:mi:ss:mmm(24小时制)

　　-----------------------------------------------------------------------

　　14 　 114 　 hh:mi:ss:mmm(24小时制)

　　------------------------------------------------------------------------

　　- 　 20 or 120 　 yyyy-mm-dd hh:mi:ss(24小时制)

　　-------------------------------------------------------------------------

　　- 　 21 or 121 　 yyyy-mm-dd hh:mi:ss:mmm(24小时制)

这样看来
就可以
如下是 按照 年月日，时分秒来分组的查询语句！
年
select avg(num),convert(varchar(4),[date],120) from dbs group by convert(varchar(4),

[date],120)
月
select avg(num),convert(varchar(7),[date],120) from dbs group by convert(varchar(7),

[date],120)
日
select avg(num),convert(varchar(10),[date],120) from dbs group by convert(varchar(10),

[date],120)
时
select avg(num),convert(varchar(10),[date],120) from dbs group by convert(varchar(10),

[date],120)
分钟
select avg(num),convert(varchar(10),[date],120) from dbs group by convert(varchar(10),

[date],120)
秒
select avg(num),convert(varchar(10),[date],120) from dbs group by convert(varchar(10),

[date],120)

列举一个按小时钟分组的结果
----------- -------------
141313      2010-02-01 00
6477        2010-02-01 01
7863        2010-02-01 02
4317        2010-02-01 03
4678        2010-02-01 04
1234        2010-02-01 05
4368        2010-02-01 06
4544        2010-02-01 07
4673        2010-02-01 08
34564       2010-02-01 09
45641       2010-02-01 10
357         2010-02-01 11
45643       2010-02-01 12
13543       2010-02-01 13
15263       2010-02-01 14
1554        2010-02-01 15
至此 ，大功告成！