SQL语句取接近整点数据

Question

上午如何取最接近整点数据 [的帖子中
dawugui 的解决方法 基本无法执行 数据少的时候还行 能够完成要求，数据一大就超时已过期 用到报表里就出现卡机报表举例
数据表 
要求是查询出每小时的最接近该整点的一条数据（这里就考虑大于整点 不考虑小于整点的取法了）
想求解SQL语句怎么实现

Answer 1

declare @tb table(dt datetime)
insert into @tb values('2009-02-24 01:00:01')
insert into @tb values('2009-02-24 01:00:02')
insert into @tb values('2009-02-24 01:00:03')
insert into @tb values('2009-02-24 02:10:01')
insert into @tb values('2009-02-24 02:10:03')
insert into @tb values('2009-02-24 03:20:11')
insert into @tb values('2009-02-24 03:03:01')--年月日小时相同
--取分秒最小的一个
select * from @tb t
where not exists
(
  select * from @tb 
  where convert(char(13),dt,120)=convert(char(13),t.dt,120) 
  and right(convert(char(19),dt,120),5)<right(convert(char(19),t.dt,120),5)
)/**
dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）
**/

Answer 2

--try:select a.* 
from tb a
join
(
select tstamp=min(tstamp)
from tb
group by convert(char(13),tstamp,120)
) b
on a.tstamp=b.tstamp

Answer 3

create table tb(dt datetime)
insert into tb values('2009-02-24 01:00:01')
insert into tb values('2009-02-24 01:00:02')
insert into tb values('2009-02-24 01:00:03')
insert into tb values('2009-02-24 02:10:01')
insert into tb values('2009-02-24 02:10:03')
insert into tb values('2009-02-24 03:20:11')
insert into tb values('2009-02-24 03:03:01')
go--1.使用子查询，然后连接
select m.* from tb m , 
(select convert(varchar(13),dt,120)+':00:00' dt, min(abs(datediff(ss , dt , convert(varchar(13),dt,120)+':00:00'))) ss from tb group by convert(varchar(13),dt,120)+':00:00') n
where convert(varchar(13),m.dt,120)+':00:00' = n.dt and abs(datediff(ss,m.dt,n.dt)) = n.ss--2.直接使用子查询，用年，月，日，小时相同，取最小的一个。
select m.* from tb m where not exists
(select 1 from tb n where convert(varchar(13),n.dt,120) = convert(varchar(13),m.dt,120) and n.dt < m.dt)--3.将子查询的数据插入临时表，然后连接取数据
--略drop table tb/*
dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）
*/

Answer 4

--你自己试试下面的哪个快,我这里数据量小,测试不出来,估计2和3快.create table tb(dt datetime)
insert into tb values('2009-02-24 01:00:01')
insert into tb values('2009-02-24 01:00:02')
insert into tb values('2009-02-24 01:00:03')
insert into tb values('2009-02-24 02:10:01')
insert into tb values('2009-02-24 02:10:03')
insert into tb values('2009-02-24 03:20:11')
insert into tb values('2009-02-24 03:03:01')
go--1.使用子查询，然后连接
select m.* from tb m , 
(select convert(varchar(13),dt,120)+':00:00' dt, min(abs(datediff(ss , dt , convert(varchar(13),dt,120)+':00:00'))) ss from tb group by convert(varchar(13),dt,120)+':00:00') n
where convert(varchar(13),m.dt,120)+':00:00' = n.dt and abs(datediff(ss,m.dt,n.dt)) = n.ss
/*
dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）
*/--2.直接使用子查询，用年，月，日，小时相同，取最小的一个。
select m.* from tb m where not exists
(select 1 from tb n where convert(varchar(13),n.dt,120) = convert(varchar(13),m.dt,120) and n.dt < m.dt)
/*
dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）
*/--3.直接使用子查询，用年，月，日，小时相同，取最小的一个。(原理和2一样，写法不一样)
select m.* from tb m where dt = 
(select min(dt) from tb n where convert(varchar(13),n.dt,120) = convert(varchar(13),m.dt,120))
/*
dt                                                     
------------------------------------------------------ 
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000（所影响的行数为 3 行）
*/--4.将子查询的数据插入临时表，然后连接取数据
--略drop table tb

Answer 5

參照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

Answer 6

在時間列建上索引
在語句上加上with(index=索引名)

Answer 7

select *,convert(char(13),dt,120) as dt1 into #t from t1select a.* 
from t1 a,(select min(dt) dt2 from #t group by dt1) b 
where a.dt=b.dt2 

Answer 8

5楼正解
create table #1(id int,portid int,tstamp datetime,immFlux int,totalFlux int,currAmount int)insert  #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495) 
insert  #1 values(3349 ,11, '2004-1-12 13:30:00', 83.32 ,1021026 ,1633) 
insert  #1 values(3350 ,11, '2004-1-12 13:54:00', 61.26 ,1021122 ,1729) 
insert  #1 values(3351 ,11, '2004-1-12 14:11:00', 63.59 ,1021191 ,1798) 
insert  #1 values(3352 ,11, '2004-1-12 14:17:00', 57.84 ,1021214 ,1821)
insert  #1 values(3353 ,11, '2004-1-12 14:35:00', 61.26 ,1021276 ,1883) 
insert  #1 values(3354 ,11, '2004-1-12 14:41:00', 60.69 ,1021299 ,1906) 
insert  #1 values(3355 ,11, '2004-1-12 15:05:00', 56.72 ,1021381 ,1988) 
insert  #1 values(3356 ,11, '2004-1-12 15:29:00', 57.84 ,1021462 ,2069) 
insert  #1 values(3357 ,11, '2004-1-12 15:52:00', 65.35 ,1021550 ,2157) 
insert  #1 values(3358 ,11, '2004-1-12 16:10:00', 66.54 ,1021621 ,2228) 
insert  #1 values(3359 ,11, '2004-1-12 16:16:00', 66.54 ,1021644 ,2251) 
insert  #1 values(3360 ,11, '2004-1-12 16:40:00', 66.54 ,1021738 ,2345) 
insert  #1 values(3361 ,11, '2004-1-12 17:03:00', 66.54 ,1021832 ,2439) 
insert  #1 values(3362 ,11, '2004-1-12 17:27:00', 66.54 ,1021927 ,2534) 
insert  #1 values(3363 ,11, '2004-1-12 17:51:00', 93.93 ,1022046 ,2653) 
insert  #1 values(3364 ,11, '2004-1-12 18:11:00', 93.26 ,1022158 ,2765) 
insert  #1 values(3365 ,11, '2004-1-12 18:14:00', 101.51, 1022179 ,2786) 
insert  #1 values(3366 ,11, '2004-1-12 18:38:00', 98.73, 1022327 ,2934) 
insert  #1 values(3367 ,11, '2004-1-12 19:02:00', 79.46, 1022451 ,3058) 
insert  #1 values(3368 ,11, '2004-1-12 19:25:00', 60.12, 1022552 ,3159) 
insert  #1 values(3369 ,11, '2004-1-12 19:49:00', 67.73, 1022647 ,3254) 
insert  #1 values(3370 ,11, '2004-1-12 20:13:00', 78.2 ,1022754 ,3361) 
insert  #1 values(3371 ,11, '2004-1-12 20:37:00', 75.06, 1022866 ,3473)
select a.* 
from #1 a
join
(
    select tstamp=min(tstamp)
    from #1
    group by convert(char(13),tstamp,120)
) b
on a.tstamp=b.tstampid          portid      tstamp                  immFlux     totalFlux   currAmount
----------- ----------- ----------------------- ----------- ----------- -----------
3348        11          2004-01-12 13:07:00.000 105         1020888     1495
3351        11          2004-01-12 14:11:00.000 63          1021191     1798
3355        11          2004-01-12 15:05:00.000 56          1021381     1988
3358        11          2004-01-12 16:10:00.000 66          1021621     2228
3361        11          2004-01-12 17:03:00.000 66          1021832     2439
3364        11          2004-01-12 18:11:00.000 93          1022158     2765
3367        11          2004-01-12 19:02:00.000 79          1022451     3058
3370        11          2004-01-12 20:13:00.000 78          1022754     3361(8 row(s) affected)

Answer 9

如果楼主的条件换成在整点附近最贴近整点的数据，而不是仅仅局限与大于整点的数据，可以使用如下方法create table #1(id int,portid int,tstamp datetime,immFlux int,totalFlux int,currAmount int)insert  #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495) 
insert  #1 values(3349 ,11, '2004-1-12 13:30:00', 83.32 ,1021026 ,1633) 
insert  #1 values(3350 ,11, '2004-1-12 13:54:00', 61.26 ,1021122 ,1729) 
insert  #1 values(3351 ,11, '2004-1-12 14:11:00', 63.59 ,1021191 ,1798) 
insert  #1 values(3352 ,11, '2004-1-12 14:17:00', 57.84 ,1021214 ,1821)
insert  #1 values(3353 ,11, '2004-1-12 14:35:00', 61.26 ,1021276 ,1883) 
insert  #1 values(3354 ,11, '2004-1-12 14:41:00', 60.69 ,1021299 ,1906) 
insert  #1 values(3355 ,11, '2004-1-12 15:05:00', 56.72 ,1021381 ,1988) 
insert  #1 values(3356 ,11, '2004-1-12 15:29:00', 57.84 ,1021462 ,2069) 
insert  #1 values(3357 ,11, '2004-1-12 15:52:00', 65.35 ,1021550 ,2157) 
insert  #1 values(3358 ,11, '2004-1-12 16:10:00', 66.54 ,1021621 ,2228) 
insert  #1 values(3359 ,11, '2004-1-12 16:16:00', 66.54 ,1021644 ,2251) 
insert  #1 values(3360 ,11, '2004-1-12 16:40:00', 66.54 ,1021738 ,2345) 
insert  #1 values(3361 ,11, '2004-1-12 17:03:00', 66.54 ,1021832 ,2439) 
insert  #1 values(3362 ,11, '2004-1-12 17:27:00', 66.54 ,1021927 ,2534) 
insert  #1 values(3363 ,11, '2004-1-12 17:51:00', 93.93 ,1022046 ,2653) 
insert  #1 values(3364 ,11, '2004-1-12 18:11:00', 93.26 ,1022158 ,2765) 
insert  #1 values(3365 ,11, '2004-1-12 18:14:00', 101.51, 1022179 ,2786) 
insert  #1 values(3366 ,11, '2004-1-12 18:38:00', 98.73, 1022327 ,2934) 
insert  #1 values(3367 ,11, '2004-1-12 19:02:00', 79.46, 1022451 ,3058) 
insert  #1 values(3368 ,11, '2004-1-12 19:25:00', 60.12, 1022552 ,3159) 
insert  #1 values(3369 ,11, '2004-1-12 19:49:00', 67.73, 1022647 ,3254) 
insert  #1 values(3370 ,11, '2004-1-12 20:13:00', 78.2 ,1022754 ,3361) 
insert  #1 values(3371 ,11, '2004-1-12 20:37:00', 75.06, 1022866 ,3473)
---创建时间点
select dd = convert(nvarchar(13),tstamp,120)+':00:00' into #2  from #1  group by convert(nvarchar(13),tstamp,120)----------连接表
select * into #3  from #1 a join #2 b on abs(datediff(minute,a.tstamp,b.dd))<30----------获得结果
select id,portid,tstamp,immflux,totalFlux,currAmount
from(
select *,rn = row_number() over (partition by dd order by abs(datediff(mm,tstamp,dd))) from #3
)t 
where rn = 1id          portid      tstamp                  immflux     totalFlux   currAmount
----------- ----------- ----------------------- ----------- ----------- -----------
3348        11          2004-01-12 13:07:00.000 105         1020888     1495
3350        11          2004-01-12 13:54:00.000 61          1021122     1729
3353        11          2004-01-12 14:35:00.000 61          1021276     1883
3357        11          2004-01-12 15:52:00.000 65          1021550     2157
3360        11          2004-01-12 16:40:00.000 66          1021738     2345
3363        11          2004-01-12 17:51:00.000 93          1022046     2653
3366        11          2004-01-12 18:38:00.000 98          1022327     2934
3369        11          2004-01-12 19:49:00.000 67          1022647     3254(8 row(s) affected)

Answer 10

又是一个教科书 MARK一下 明天早餐时间来观摩

Answer 11

以理这个SQL简单且效率高点：select *
  from tb a,
      (select min(tstamp) tstamp from tb group by convert(varchar(8),tstamp,112) + datepart(hour,tstamp)) b
 where a.tstamp = b.tstamp