时间分段查询，每段时间获取另一字段替最小的时间

时间分段查询，每段时间获取另一字段为最小的时间
还是之前提问过的
http://bbs.csdn.net/topics/390277980
需求发生了变化
首先是原始表增加了Num字段
doer dotime        num
 a 2012-1-1 0:09    12.2
 a 2012-1-1 1:0912.6
 a 2012-1-1 2:0911.9
 a 2012-1-1 3:099.6
 a 2012-1-1 4:0912.6
 a 2012-1-1 5:0911.9
 a 2012-1-1 6:099.6
 a 2012-1-1 7:0912.6
 a 2012-1-1 8:0911.9
 a 2012-1-1 9:099.6
 a 2012-1-1 10:0912.6
 a 2012-1-1 11:0911.9
 a 2012-1-1 12:099.6
 a 2012-1-1 13:0912.6
 a 2012-1-1 14:0911.9
 a 2012-1-1 15:099.6
 a 2012-1-1 16:099.6
 a 2012-1-1 17:0912.6
 a 2012-1-1 18:0911.9
 a 2012-1-1 19:099.6
 a 2012-1-1 20:0912.6
 a 2012-1-1 21:0911.9
 a 2012-1-1 22:099.6
 a 2012-1-1 23:0912.6
 b 2012-1-1 0:2011.9
 b 2012-1-1 2:209.6
 b 2012-1-1 4:2012.6
 b 2012-1-1 6:2011.9
 b 2012-1-1 8:209.6
 b 2012-1-1 10:2012.6
 b 2012-1-1 12:2011.9
 b 2012-1-1 14:209.6
 b 2012-1-1 16:209.6
 b 2012-1-1 18:2012.6
 b 2012-1-1 20:2011.9
 b 2012-1-1 22:209.6

把一天时间分成若干时间段，这里假设为6个，每4小时为一个时间段
 针对每个Doer的每天的操作，将各个时间段的num最小的记录对应的第一个时间填充到对应自段中，形成类似下面的表结构
doer 0-4               4-8            8-12...
 a    2012-1-1 0:09 2012-1-1 4:09 2012-1-1 8:09 ...
 b    2012-1-1 0:20 2012-1-1 4:20 2012-1-1 8:20 ... 
[最优解释]
select doer,convert(varchar(10),dotime,120) as rq 
         [0-4]=(select top 1 dotime from tb 
              where convert(varchar(10),dotime,120)=convert(varchar(10),a.dotime,120) 
              and datepart(hh,dotime) between 0 and 4 order by num),
         [4-8]=(select top 1 dotime from tb 
              where convert(varchar(10),dotime,120)=convert(varchar(10),a.dotime,120) 
              and datepart(hh,dotime) between 4 and 8 order by num),
            ..............
  from tb a group by doer,convert(varchar(10),dotime,120)  
[其他解释]

----------------------------
-- Author  :TravyLee(物是人非事事休,欲语泪先流!)
-- Date    :2012-11-20 09:17:19
-- 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]([doer] varchar(1),[dotime] datetime,[num] numeric(3,1))
insert [test]
select 'a','2012-1-1 0:09',12.2 union all
select 'a','2012-1-1 1:09',12.6 union all
select 'a','2012-1-1 2:09',11.9 union all
select 'a','2012-1-1 3:09',9.6 union all
select 'a','2012-1-1 4:09',12.6 union all
select 'a','2012-1-1 5:09',11.9 union all
select 'a','2012-1-1 6:09',9.6 union all
select 'a','2012-1-1 7:09',12.6 union all
select 'a','2012-1-1 8:09',11.9 union all
select 'a','2012-1-1 9:09',9.6 union all
select 'a','2012-1-1 10:09',12.6 union all
select 'a','2012-1-1 11:09',11.9 union all
select 'a','2012-1-1 12:09',9.6 union all
select 'a','2012-1-1 13:09',12.6 union all
select 'a','2012-1-1 14:09',11.9 union all
select 'a','2012-1-1 15:09',9.6 union all
select 'a','2012-1-1 16:09',9.6 union all
select 'a','2012-1-1 17:09',12.6 union all
select 'a','2012-1-1 18:09',11.9 union all
select 'a','2012-1-1 19:09',9.6 union all
select 'a','2012-1-1 20:09',12.6 union all
select 'a','2012-1-1 21:09',11.9 union all
select 'a','2012-1-1 22:09',9.6 union all
select 'a','2012-1-1 23:09',12.6 union all
select 'b','2012-1-1 0:20',11.9 union all
select 'b','2012-1-1 2:20',9.6 union all
select 'b','2012-1-1 4:20',12.6 union all
select 'b','2012-1-1 6:20',11.9 union all
select 'b','2012-1-1 8:20',9.6 union all
select 'b','2012-1-1 10:20',12.6 union all
select 'b','2012-1-1 12:20',11.9 union all
select 'b','2012-1-1 14:20',9.6 union all
select 'b','2012-1-1 16:20',9.6 union all
select 'b','2012-1-1 18:20',12.6 union all
select 'b','2012-1-1 20:20',11.9 union all
select 'b','2012-1-1 22:20',9.6
go

;with t
as(
select
*,
case when convert(varchar(8),[dotime],24) between '00:00:00' and '03:59:59' then '[0-4]'
when convert(varchar(8),[dotime],24) between '04:00:00' and '07:59:59' then '[4-8]'
when convert(varchar(8),[dotime],24) between '08:00:00' and '11:59:59' then '[8-12]'
when convert(varchar(8),[dotime],24) between '12:00:00' and '15:59:59' then '[12-16]'
when convert(varchar(8),[dotime],24) between '16:00:00' and '19:59:59' then '[16-20]'
else '[20-24]' end as [时间段]
from
test
)
select
[doer],
min(case when [时间段]='[0-4]' then [dotime] end) as '[0-4]',
min(case when [时间段]='[4-8]' then [dotime] end) as '[4-8]', 
 
min(case when [时间段]='[8-12]' then [dotime] end) as '[8-12]',
min(case when [时间段]='[12-16]' then [dotime] end) as '[12-16]',
min(case when [时间段]='[16-20]' then [dotime] end) as '[16-20]',
min(case when [时间段]='[20-24]' then [dotime] end) as '[20-24]'
from
t
group by
[doer]
/*
doer [0-4]                   [4-8]                   [8-12]                  [12-16]                 [16-20]                 [20-24]
---- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
a    2012-01-01 00:09:00.000 2012-01-01 04:09:00.000 2012-01-01 08:09:00.000 2012-01-01 12:09:00.000 2012-01-01 16:09:00.000 2012-01-01 20:09:00.000
b    2012-01-01 00:20:00.000 2012-01-01 04:20:00.000 2012-01-01 08:20:00.000 2012-01-01 12:20:00.000 2012-01-01 16:20:00.000 2012-01-01 20:20:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。

(2 行受影响)


*/

[其他解释]

引用:

版主，我仔细看了，“将各个时间段的num最小的记录对应的第一个时间填充到对应自段中”
好像没有体现啊

额 没注意  楼上的那个行不行？
[其他解释]

引用:

没问题，不知上万条记录速度如何

你的结果就没给对啊

----------------------------
-- Author  :TravyLee(物是人非事事休,欲语泪先流!)
-- Date    :2012-11-20 09:17:19
-- 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]([doer] varchar(1),[dotime] datetime,[num] numeric(3,1))
insert [test]
select 'a','2012-1-1 0:09',12.2 union all
select 'a','2012-1-1 1:09',12.6 union all
select 'a','2012-1-1 2:09',11.9 union all
select 'a','2012-1-1 3:09',9.6 union all
select 'a','2012-1-1 4:09',12.6 union all
select 'a','2012-1-1 5:09',11.9 union all
select 'a','2012-1-1 6:09',9.6 union all
select 'a','2012-1-1 7:09',12.6 union all
select 'a','2012-1-1 8:09',11.9 union all
select 'a','2012-1-1 9:09',9.6 union all
select 'a','2012-1-1 10:09',12.6 union all 
 
select 'a','2012-1-1 11:09',11.9 union all
select 'a','2012-1-1 12:09',9.6 union all
select 'a','2012-1-1 13:09',12.6 union all
select 'a','2012-1-1 14:09',11.9 union all
select 'a','2012-1-1 15:09',9.6 union all
select 'a','2012-1-1 16:09',9.6 union all
select 'a','2012-1-1 17:09',12.6 union all
select 'a','2012-1-1 18:09',11.9 union all
select 'a','2012-1-1 19:09',9.6 union all
select 'a','2012-1-1 20:09',12.6 union all
select 'a','2012-1-1 21:09',11.9 union all
select 'a','2012-1-1 22:09',9.6 union all
select 'a','2012-1-1 23:09',12.6 union all
select 'b','2012-1-1 0:20',11.9 union all
select 'b','2012-1-1 2:20',9.6 union all
select 'b','2012-1-1 4:20',12.6 union all
select 'b','2012-1-1 6:20',11.9 union all
select 'b','2012-1-1 8:20',9.6 union all
select 'b','2012-1-1 10:20',12.6 union all
select 'b','2012-1-1 12:20',11.9 union all
select 'b','2012-1-1 14:20',9.6 union all
select 'b','2012-1-1 16:20',9.6 union all
select 'b','2012-1-1 18:20',12.6 union all
select 'b','2012-1-1 20:20',11.9 union all
select 'b','2012-1-1 22:20',9.6
go

go
;with t
as(
select
*,
case when convert(varchar(8),[dotime],24) between '00:00:00' and '03:59:59' then '[0-4]'
when convert(varchar(8),[dotime],24) between '04:00:00' and '07:59:59' then '[4-8]'
when convert(varchar(8),[dotime],24) between '08:00:00' and '11:59:59' then '[8-12]'
when convert(varchar(8),[dotime],24) between '12:00:00' and '15:59:59' then '[12-16]'
when convert(varchar(8),[dotime],24) between '16:00:00' and '19:59:59' then '[16-20]'
else '[20-24]' end as [时间段]
from
test
),
m as(
select
distinct doer,
时间段,
(select top 1 [dotime] from t where t.doer=a.doer and t.时间段=a.时间段 order by num asc) as [dotime]
from 
t a
)
select
doer,
max(case when 时间段='[0-4]' then [dotime] end) as [0-4],
max(case when 时间段='[4-8]' then [dotime] end) as [4-8],
max(case when 时间段='[8-12]' then [dotime] end) as [8-12],
max(case when 时间段='[12-16]' then [dotime] end) as [12-16],
max(case when 时间段='[16-20]' then [dotime] end) as [16-20],
max(case when 时间段='[20-24]' then [dotime] end) as [20-24]
from 
m
group by
doer


/*
doer 0-4                     4-8                     8-12                    12-16                   16-20                   20-24 
 
---- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
a    2012-01-01 03:09:00.000 2012-01-01 06:09:00.000 2012-01-01 09:09:00.000 2012-01-01 12:09:00.000 2012-01-01 16:09:00.000 2012-01-01 22:09:00.000
b    2012-01-01 02:20:00.000 2012-01-01 06:20:00.000 2012-01-01 08:20:00.000 2012-01-01 14:20:00.000 2012-01-01 16:20:00.000 2012-01-01 22:20:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。

(2 行受影响)




*/

[其他解释]
该回复于2012-11-20 09:15:18被版主删除
[其他解释]
该回复于2012-11-20 09:15:44被版主删除
[其他解释]
红帽哥发火了
[其他解释]

引用:

你连一个数学的必要条件，或是勾股定理你都不懂。
你还做什么开发啊。

这你都看出来了，实在汗颜啊
[其他解释]
十分感谢楼上的
记录非常多，每天可能上万条记录，速度不知如何
[其他解释]
版主，我仔细看了，“将各个时间段的num最小的记录对应的第一个时间填充到对应自段中”
好像没有体现啊
[其他解释]
没问题，不知上万条记录速度如何
[其他解释]
ssp2009好像有点问题，提示doer为不支持的数据类型
[其他解释]
我忘了说了，是在设计视图时出现的<不支持的数据类型>问题，我想把相关查询结果生成视图
[其他解释]
另外请问，在子查询中是否需要加上doer=a.doer?
  [0-4]=(select top 1 dotime from tb 
               where doer=a.doer and convert(varchar(10),dotime,120)=convert(varchar(10),a.dotime,120) 
               and datepart(hh,dotime) between 0 and 4 order by num),

查看更多 下一篇