急！求SQL语句
表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
-----------------------------------
[解决办法]
create table #(ID int, StartTime varchar(20))
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 sysobjects
select 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
----
00
10
20
30
40
50
60
70
80
90
100
110
121
131
140
152
160
170
180
190
200
210
220
230
[解决办法]
Declare @表A table(ID int, StartTime varchar(20))
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
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create 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
go
create 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 行）
*/
