SQL日期筛选的问题

[SilverNet]

有如下数据：
datetime
-----------------
2010-01-01 1:00:00
2010-01-01 1:01:00
2010-01-02 1:01:00
2010-01-02 2:01:00
2010-01-02 5:01:00
2010-01-02 10:01:00
................
2010-01-02 23:00:00
2010-01-02 23:50:00
----------------------

datetime字段包含的数据都是随机的，
现在我想取出“年-月-日 小时”格式的数据，并保证这条数据是唯一的。
如：
datetime
-------------------
2010-01-01 1:00:00
2010-01-02 2:00:00
2010-01-02 5:00:00
2010-01-02 10:00:00
......................
2010-01-02 23:00:00

以及取出缺失的日期，根据“年-月-日 小时”格式来取，如：
datetime
--------------------
2010-01-01 2:00
2010-01-01 3:00
2010-01-01 4:00
.............

[飘零一叶]

create table tb
(
  rq datetime
)
insert into tb
select '2010-01-01 1:00:00' union all
select '2010-01-01 1:01:00' union all
select '2010-01-02 1:01:00' union all
select '2010-01-02 2:01:00' union all
select '2010-01-02 5:01:00' union all
select '2010-01-02 10:01:00'


--1.
select CONVERT(varchar(14),rq,120)+'00:00'
from tb group by CONVERT(varchar(14),rq,120)
/*
------------------
(无列名)
2010-01-01 01:00:00
2010-01-02 01:00:00
2010-01-02 02:00:00
2010-01-02 05:00:00
2010-01-02 10:00:00
*/

--2.
select aa.* from
(select dateadd(hh,number,CONVERT(varchar(10),rq,120)) as rq
from master..spt_values,(select CONVERT(varchar(10),rq,120) as rq from tb group by CONVERT(varchar(10),rq,120))b
where type='p' and number between 1 and 12) aa left join tb bb on DATEDIFF(hh,aa.rq,bb.rq)=0
where bb.rq is null
/*
----------------------------
rq
2010-01-01 02:00:00.000
2010-01-01 03:00:00.000
2010-01-01 04:00:00.000
2010-01-01 05:00:00.000
2010-01-01 06:00:00.000
2010-01-01 07:00:00.000
2010-01-01 08:00:00.000
2010-01-01 09:00:00.000
2010-01-01 10:00:00.000
2010-01-01 11:00:00.000
2010-01-01 12:00:00.000
2010-01-02 03:00:00.000
2010-01-02 04:00:00.000
2010-01-02 06:00:00.000
2010-01-02 07:00:00.000
2010-01-02 08:00:00.000
2010-01-02 09:00:00.000
2010-01-02 11:00:00.000
2010-01-02 12:00:00.000
*/

[飘零一叶]

select aa.* from
(select dateadd(hh,number,CONVERT(varchar(10),rq,120)) as rq
from master..spt_values,(select CONVERT(varchar(10),rq,120) as rq from tb group by CONVERT(varchar(10),rq,120))b
where type='p' and number between 1 and 24) aa left join tb bb on DATEDIFF(hh,aa.rq,bb.rq)=0
where bb.rq is null

修改下 一天24小时

[gogodiy]

create table t1
(
	riqi datetime
)
insert into t1
select '2010-01-01 1:00:00' union all
select '2010-01-01 1:01:00' union all
select '2010-01-02 1:01:00' union all
select '2010-01-02 2:01:00' union all
select '2010-01-02 5:01:00' union all
select '2010-01-02 10:01:00' union all
select '2010-01-02 23:00:00' union all
select '2010-01-02 23:50:00'

select distinct left(convert(varchar,riqi,120),13) +':00:00' from t1

[luoyefeng007]

都强！！

[dawugui]

create table tb
(
  rq datetime
)
insert into tb
select '2010-01-01 1:00:00' union all
select '2010-01-01 1:01:00' union all
select '2010-01-02 1:01:00' union all
select '2010-01-02 2:01:00' union all
select '2010-01-02 5:01:00' union all
select '2010-01-02 10:01:00'

go

--现在我想取出“年-月-日 小时”格式的数据，并保证这条数据是唯一的。
select distinct convert(varchar(13),rq,120) + ':00:00' as rq from tb 

/*
rq                  
------------------- 
2010-01-01 01:00:00
2010-01-02 01:00:00
2010-01-02 02:00:00
2010-01-02 05:00:00
2010-01-02 10:00:00

（所影响的行数为 5 行）
*/

drop table tb

[dawugui]

create table tb
(
  rq datetime
)
insert into tb
select '2010-01-01 1:00:00' union all
select '2010-01-01 1:01:00' union all
select '2010-01-02 1:01:00' union all
select '2010-01-02 2:01:00' union all
select '2010-01-02 5:01:00' union all
select '2010-01-02 10:01:00'

go

--现在我想取出“年-月-日 小时”格式的数据，并保证这条数据是唯一的。
select distinct convert(varchar(13),rq,120) + ':00:00' as rq from tb 

/*
rq                  
------------------- 
2010-01-01 01:00:00
2010-01-02 01:00:00
2010-01-02 02:00:00
2010-01-02 05:00:00
2010-01-02 10:00:00

（所影响的行数为 5 行）
*/

--以及取出缺失的日期，根据“年-月-日 小时”格式来取，如：
select t1.rq from
(
select m.rq + ' ' + n.hh rq from
(select distinct convert(varchar(10),rq,120) as rq from tb) m,
( 
select '00:00:00' hh union all
select '01:00:00' hh union all
select '02:00:00' hh union all
select '03:00:00' hh union all
select '04:00:00' hh union all
select '05:00:00' hh union all
select '06:00:00' hh union all
select '07:00:00' hh union all
select '08:00:00' hh union all
select '09:00:00' hh union all
select '10:00:00' hh union all
select '11:00:00' hh union all
select '12:00:00' hh union all
select '13:00:00' hh union all
select '14:00:00' hh union all
select '15:00:00' hh union all
select '16:00:00' hh union all
select '17:00:00' hh union all
select '18:00:00' hh union all
select '19:00:00' hh union all
select '20:00:00' hh union all
select '21:00:00' hh union all
select '22:00:00' hh union all
select '23:00:00' hh ) n
) t1 where rq not in 
(select distinct convert(varchar(13),rq,120) + ':00:00' as rq from tb)
order by t1.rq
/*
rq                  
------------------- 
2010-01-01 00:00:00
2010-01-01 02:00:00
2010-01-01 03:00:00
2010-01-01 04:00:00
2010-01-01 05:00:00
2010-01-01 06:00:00
2010-01-01 07:00:00
2010-01-01 08:00:00
2010-01-01 09:00:00
2010-01-01 10:00:00
2010-01-01 11:00:00
2010-01-01 12:00:00
2010-01-01 13:00:00
2010-01-01 14:00:00
2010-01-01 15:00:00
2010-01-01 16:00:00
2010-01-01 17:00:00
2010-01-01 18:00:00
2010-01-01 19:00:00
2010-01-01 20:00:00
2010-01-01 21:00:00
2010-01-01 22:00:00
2010-01-01 23:00:00
2010-01-02 00:00:00
2010-01-02 03:00:00
2010-01-02 04:00:00
2010-01-02 06:00:00
2010-01-02 07:00:00
2010-01-02 08:00:00
2010-01-02 09:00:00
2010-01-02 11:00:00
2010-01-02 12:00:00
2010-01-02 13:00:00
2010-01-02 14:00:00
2010-01-02 15:00:00
2010-01-02 16:00:00
2010-01-02 17:00:00
2010-01-02 18:00:00
2010-01-02 19:00:00
2010-01-02 20:00:00
2010-01-02 21:00:00
2010-01-02 22:00:00
2010-01-02 23:00:00

（所影响的行数为 43 行）
*/

drop table tb

[SilverNet]

[Quote=引用 2 楼 dlut_liuq 的回复:]

SQL code
select aa.* from
(select dateadd(hh,number,CONVERT(varchar(10),rq,120)) as rq
from master..spt_values,(select CONVERT(varchar(10),rq,120) as rq from tb group by CONVERT(varchar(10),rq,120))……
[/Quote]

明白了，其实是利用master..spt_values表的数字数据，用Dateadd函数做时间累加，这样就可以求出一段时间的有格式数据。

[SilverNet]

[Quote=引用 4 楼 dawugui 的回复:]

SQL code
create table tb
(
rq datetime
)
insert into tb
select '2010-01-01 1:00:00' union all
select '2010-01-01 1:01:00' union all
select '2010-01-02 1:01:00' union all
select '2010-01-02 2:01:00……
[/Quote]
dawugui兄，你的方法也很好，不过考虑到表中可能某一天的数据全部缺失，这样就会少一天的日期没有查出来的情况。不过主要是学习了方法，呵呵