表A
--------------------------
ID StartTime
1 12:30
2 13:50
3 15:05
4 15:40
--------------------------
要求得到统计结果如下,count为记录条数
------------------------------------
Time Count
0(0:00--0:59) 0
1(1:00--1:59) 0
...
12(12:00--12:59) 1
13(13:00--13:59) 1
14(14:00--14:59) 0
15(15:00--15:59) 2
...
23(23:00--23:59) 0
-----------------------------------
--------------------------
ID StartTime
1 12:30
2 13:50
3 15:05
4 15:40
--------------------------
要求得到统计结果如下,count为记录条数
------------------------------------
Time Count
0(0:00--0:59) 0
1(1:00--1:59) 0
...
12(12:00--12:59) 1
13(13:00--13:59) 1
14(14:00--14:59) 0
15(15:00--15:59) 2
...
23(23:00--23:59) 0
-----------------------------------
from (
Select Left(ltrim(StartTime),Charindex(':',StartTime)-1) as Time,
Count=count(*)
from 表A group by Left(ltrim(StartTime),2) ) as a
Right Join (Select top 24 colID-1 as rID from syscolumns
where id in (Select top 1 ID from syscolumns group by ID
having count(*)>=24 ) order by rID) as b
on Convert(Integer,a.Time)=b.rID
insert into # select 1 , '12:30' union all
select 2 , '13:50' union all
select 3 , '15:05' union all
select 4 , '15:40' select top 24 time=identity(int,0,1) into #1 from sysobjectsselect cast(time as int) as time,count(id) as [count] from # right join #1 on left(#.StartTime,2)=#1.time group by time order by time
----0 0
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 1
13 1
14 0
15 2
16 0
17 0
18 0
19 0
20 0
21 0
22 0
23 0
----------------------------------
表B
----------------------------------------------
Name StarTime EndTime
0(0:00--0:59) 0:00 0:59
...
23(23:00--23:59) 23:00 23:59
----------------------------------------------select C.Name,count(1)
from
(select B.Name from B
left join A
on A.StartTime between B.StartTime to B.EndTime) C
Group by C.name
----------------------------------
表B
----------------------------------------------
Name StarTime EndTime
0(0:00--0:59) 0:00 0:59
...
23(23:00--23:59) 23:00 23:59
----------------------------------------------select C.Name,count(1)
from
(select B.Name from B
left join A
on A.StartTime between B.StartTime to B.EndTime) C
Group by C.name
Insert into @表A
select 1 , '12:40' union all
select 2 , '13:20' union all
select 3 , '15:15' union all
select 4 , '15:55' union all
select 5 , '8:10' union all
select 6 , '1:50' Select b.rID as Time,IsNull(Count,0) as Count
from (
Select Left(ltrim(StartTime),Charindex(':',StartTime)-1) as Time,
Count=count(*)
from @表A group by Left(ltrim(StartTime),Charindex(':',StartTime)-1) ) as a
Right Join (Select top 24 colID-1 as rID from syscolumns
where id in (Select top 1 ID from syscolumns group by ID
having count(*)>=24 ) order by rID) as b
on Convert(Integer,a.Time)=b.rID
drop table tb
gocreate table tb
(
hour int,
memo1 varchar(10),
memo2 varchar(10)
)insert into tb(hour,memo1,memo2) values(0, '0:00', '0:59')
insert into tb(hour,memo1,memo2) values(1, '1:00', '1:59')
insert into tb(hour,memo1,memo2) values(2, '2:00', '2:59')
insert into tb(hour,memo1,memo2) values(3, '3:00', '3:59')
insert into tb(hour,memo1,memo2) values(4, '4:00', '4:59')
insert into tb(hour,memo1,memo2) values(5, '5:00', '5:59')
insert into tb(hour,memo1,memo2) values(6, '6:00', '6:59')
insert into tb(hour,memo1,memo2) values(7, '7:00', '7:59')
insert into tb(hour,memo1,memo2) values(8, '8:00', '8:59')
insert into tb(hour,memo1,memo2) values(9, '9:00', '9:59')
insert into tb(hour,memo1,memo2) values(10,'10:00','10:59')
insert into tb(hour,memo1,memo2) values(11,'11:00','11:59')
insert into tb(hour,memo1,memo2) values(12,'12:00','12:59')
insert into tb(hour,memo1,memo2) values(13,'13:00','13:59')
insert into tb(hour,memo1,memo2) values(14,'14:00','14:59')
insert into tb(hour,memo1,memo2) values(15,'15:00','15:59')
insert into tb(hour,memo1,memo2) values(16,'16:00','16:59')
insert into tb(hour,memo1,memo2) values(17,'17:00','17:59')
insert into tb(hour,memo1,memo2) values(18,'18:00','18:59')
insert into tb(hour,memo1,memo2) values(19,'19:00','19:59')
insert into tb(hour,memo1,memo2) values(20,'20:00','20:59')
insert into tb(hour,memo1,memo2) values(21,'21:00','21:59')
insert into tb(hour,memo1,memo2) values(22,'22:00','22:59')
insert into tb(hour,memo1,memo2) values(23,'23:00','23:59')if object_id('pubs..A') is not null
drop table A
gocreate table A
(
ID int,
StartTime varchar(10)
)insert into A(ID,StartTime) values(1,'12:30')
insert into A(ID,StartTime) values(2,'13:50')
insert into A(ID,StartTime) values(3,'15:05')
insert into A(ID,StartTime) values(4,'15:40')select cast(tb.hour as varchar(10)) + '(' + tb.memo1 + '--' + tb.memo2 + ')' as Time,
sum(case when a.starttime >= tb.memo1 and a.starttime <= tb.memo2 then 1 else 0 end) as count
from tb
left join a on a.starttime >= tb.memo1 and a.starttime <= tb.memo2
group by cast(tb.hour as varchar(10)) + '(' + tb.memo1 + '--' + tb.memo2 + ')'drop table tb,A/*result
Time count
---------------------------------- -----------
0(0:00--0:59) 0
1(1:00--1:59) 0
10(10:00--10:59) 0
11(11:00--11:59) 0
12(12:00--12:59) 1
13(13:00--13:59) 1
14(14:00--14:59) 0
15(15:00--15:59) 2
16(16:00--16:59) 0
17(17:00--17:59) 0
18(18:00--18:59) 0
19(19:00--19:59) 0
2(2:00--2:59) 0
20(20:00--20:59) 0
21(21:00--21:59) 0
22(22:00--22:59) 0
23(23:00--23:59) 0
3(3:00--3:59) 0
4(4:00--4:59) 0
5(5:00--5:59) 0
6(6:00--6:59) 0
7(7:00--7:59) 0
8(8:00--8:59) 0
9(9:00--9:59) 0(所影响的行数为 24 行)*/