MFC下SQL Server查询分析数据分析（求助！！！急）
最近有一个项目苦恼ING，求各位大牛们帮帮忙，谢谢啦！：
现在SQL Server数据库里有所有车一天的数据（可能有几百万条），每个车的平均上传数据时间并不是精准的每30秒（也有可能10秒 、12秒等上传一次，也可能大于30秒）。也就是说所有车30秒左右传一次车的状态变化情况，假如00：00：12和00：00：27时刻分别传一条数据，以小于30秒的最后一条记录为准。
有如下要求：
根据输入的日期如：‘2012-11-28’在程序中实现如下效果（要一天24小时的，差不多有2880条）：
时间 满载车辆数 空载车辆数
0：00：00 500 600
0：00：30 530 570
0：01：00 580 520
0：01：30 600 500
数据库数据如下（同时有很多车上传数据，每台车都有一天24小时的数据，0为空车，1为满）：
T_CarID T_dateTime T_Status
1 2012-11-28 00:00:12.000 0
1 2012-11-28 00:00:27.000 0
1 2012-11-28 00:00:37.000 0
1 2012-11-28 00:01:02.000 0
1 2012-11-28 00:01:27.000 0
1 2012-11-28 00:01:52.000 1
1 2012-11-28 00:02:17.000 1
现在遇到的问题就是假如我输入2012-11-28，怎么将数据分析出来，如00：00：00-00：00：30内的所有车的状态显示如上
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-27 14:26:45
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[T_CarID] int,
[T_dateTime] datetime,
[T_Status] int
)
insert [test]
select 1,'2012-11-28 00:00:12.000',0 union all
select 1,'2012-11-28 00:00:27.000',0 union all
select 1,'2012-11-28 00:00:30.000',0 union all
select 1,'2012-11-28 00:01:02.000',0 union all
select 1,'2012-11-28 00:01:27.000',0 union all
select 1,'2012-11-28 00:01:52.000',1 union all
select 1,'2012-11-28 00:02:17.000',1
go
with T(a)
as(
select 1 union all
select 1 union all
select 1
),m
as(
select a.a from t a,t b
),
n
as(
select a.a from m a,m b
),
o
as(
select a.a from n a,n b
),
p
as(
select a.a from o a,n b
),
tb as
(
select top 86370 number=ROW_NUMBER()over(order by (select 1))
from p
),
datetimes
as(
select
ROW_NUMBER()over(order by (getdate())) as px,
convert(varchar(8),DATEADD(SECOND,number,'00:00:00'),108) as dt
from
tb
where
number between 0 and DATEDIFF(SECOND,'00:00:00','23:59:30')
and number%30=0
)
select top 30 * from datetimes
/*
pxdt
-------------------------
100:00:30
200:01:00
300:01:30
400:02:00
500:02:30
600:03:00
700:03:30
800:04:00
900:04:30
1000:05:00
1100:05:30
1200:06:00
1300:06:30
1400:07:00
1500:07:30
1600:08:00
1700:08:30
1800:09:00
1900:09:30
2000:10:00
2100:10:30
2200:11:00
2300:11:30
2400:12:00
2500:12:30
2600:13:00
2700:13:30
2800:14:00
2900:14:30
3000:15:00
*/
--你就是这个地方不会做？
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-27 14:26:45
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[T_CarID] int,
[T_dateTime] datetime,
[T_Status] int
)
insert [test]
select 1,'2012-11-28 00:00:12.000',0 union all
select 1,'2012-11-28 00:00:27.000',0 union all
select 1,'2012-11-28 00:00:30.000',0 union all
select 1,'2012-11-28 00:01:02.000',0 union all
select 1,'2012-11-28 00:01:27.000',0 union all
select 1,'2012-11-28 00:01:52.000',1 union all
select 1,'2012-11-28 00:02:17.000',1
go
--我先生成一个时间序列表吧
with T(a)
as(
select 1 union all
select 1 union all
select 1
),m
as(
select a.a from t a,t b
),
n
as(
select a.a from m a,m b
)
,o
as(
select a.a from n a,n b
),
p
as(
select a.a from o a,n b
),
tb as
(
select top 86370 number=ROW_NUMBER()over(order by (select 1))
from p
),
datetimes
as(
select
ROW_NUMBER()over(order by (getdate())) as px,
convert(varchar(8),DATEADD(SECOND,number,'00:00:00'),108) as dt
from
tb
where
number between 0 and DATEDIFF(SECOND,'00:00:00','23:59:30')
and number%30=0
)
select * into times from datetimes
go
;with t
as(
select
case DATEDIFF(SECOND,'2012-11-28 00:00:00.000',[T_dateTime])%30
when 0 then (DATEDIFF(SECOND,'2012-11-28 00:00:00.000',[T_dateTime])/30)
else (DATEDIFF(SECOND,'2012-11-28 00:00:00.000',[T_dateTime])/30)+1 end as px,
*
from
test
)
select
a.dt as 时间,
sum(case when t.T_Status=0 then 1 else 0 end) as 空载车辆数,
sum(case when t.T_Status=1 then 1 else 0 end) as 满载车辆数
from
t ,times a
where
a.px=t.px
group by
a.dt
/*
时间空载车辆数满载车辆数
------------------------------------
00:00:3030
00:01:3020
00:02:0001
00:02:3001
*/
