declare @tb table ( [ 日期 ] varchar ( 3 ), [ 工作时间 ] varchar ( 5 ))
insert @tb
select ' 1号 ' , ' 3:10 ' union all
select ' 2号 ' , ' 3:20 ' union all
select ' 3号 ' , ' 4:20 ' union all
select ' 4号 ' , ' 4:30 '
select convert ( varchar ( 5 ), dateadd (mi, avg ( datediff (mi, 0 ,工作时间)), 0 ), 108 ) from @tb
-- mi 分钟
/*
-----
03:50
--每天的每个小时
SELECT
CONVERT(
VARCHAR(
13), CreateTime,
120)
[
hour
],
COUNT( *) cnt
FROM Business_Login
WHERE CreateTime > ' 2011-11-22 '
GROUP BY
CONVERT( VARCHAR( 13), CreateTime, 120)
/*
2011-11-22 09 44
2011-11-22 10 48
2011-11-22 11 35
2011-11-22 12 34
2011-11-22 13 42
2011-11-22 14 33
2011-11-22 15 21
2011-11-22 16 45
2011-11-22 17 46
2011-11-22 18 6
*/
-- 按0,1,2...
SELECT DATEPART(hh, CreateTime) [ hour ],
COUNT( *) cnt
FROM Business_Login
WHERE CreateTime > ' 2011-11-21 '
GROUP BY
DATEPART(hh, CreateTime)
/*
9 44
10 48
11 35
12 34
13 61
14 73
15 32
16 64
17 99
18 15
*/
COUNT( *) cnt
FROM Business_Login
WHERE CreateTime > ' 2011-11-22 '
GROUP BY
CONVERT( VARCHAR( 13), CreateTime, 120)
/*
2011-11-22 09 44
2011-11-22 10 48
2011-11-22 11 35
2011-11-22 12 34
2011-11-22 13 42
2011-11-22 14 33
2011-11-22 15 21
2011-11-22 16 45
2011-11-22 17 46
2011-11-22 18 6
*/
-- 按0,1,2...
SELECT DATEPART(hh, CreateTime) [ hour ],
COUNT( *) cnt
FROM Business_Login
WHERE CreateTime > ' 2011-11-21 '
GROUP BY
DATEPART(hh, CreateTime)
/*
9 44
10 48
11 35
12 34
13 61
14 73
15 32
16 64
17 99
18 15
*/