当前位置: 代码迷 >> Sql Server >> TravyLee,你昨天帮小弟我写的SQL有点小疑点,帮忙再看看,多谢
  详细解决方案

TravyLee,你昨天帮小弟我写的SQL有点小疑点,帮忙再看看,多谢

热度:62   发布时间:2016-04-27 13:32:09.0
TravyLee,你昨天帮我写的SQL有点小问题,帮忙再看看,谢谢!
create table test
(
id int,---编号
sl int,---数量
rq datetime ---日期
)

insert into test select 10, 101,'2002-1-1 6:00:00' ---注意,这条记录不应算在2002-01-01这一天里面,每一天的时间是从6:10开始的,到第二天的6:00结束

-------------------------------------------------------
insert into test select 10, 100,'2002-1-1 6:10:00'
insert into test select 10, 100,'2002-1-1 6:20:00'
insert into test select 10, 100,'2002-1-1 6:30:00'
insert into test select 10, 100,'2002-1-1 6:40:00'
insert into test select 10, 100,'2002-1-1 6:50:00'
insert into test select 10, 100,'2002-1-1 7:00:00'



insert into test select 10, 100,'2002-1-1 23:10:00'
insert into test select 10, 100,'2002-1-1 23:20:00'
insert into test select 10, 100,'2002-1-1 23:30:00'
insert into test select 10, 100,'2002-1-1 23:40:00'
insert into test select 10, 100,'2002-1-1 23:50:00'
insert into test select 10, 100,'2002-1-2 00:00:00'


insert into test select 10, 100,'2002-1-2 00:10:00'
insert into test select 10, 100,'2002-1-2 00:20:00'
insert into test select 10, 100,'2002-1-2 00:30:00'
insert into test select 10, 100,'2002-1-2 00:40:00'
insert into test select 10, 100,'2002-1-2 00:50:00'
insert into test select 10, 100,'2002-1-2 01:00:00'


insert into test select 10, 100,'2002-1-2 05:10:00'
insert into test select 10, 100,'2002-1-2 05:20:00'
insert into test select 10, 100,'2002-1-2 05:30:00'
insert into test select 10, 100,'2002-1-2 05:40:00'
insert into test select 10, 100,'2002-1-2 05:50:00'
insert into test select 10, 100,'2002-1-2 06:00:00'

----------------------------------------------------

insert into test select 10, 105,'2002-1-2 6:10:00'
insert into test select 10, 100,'2002-1-2 6:20:00'
insert into test select 10, 100,'2002-1-2 6:30:00'
insert into test select 10, 100,'2002-1-2 6:40:00'
insert into test select 10, 100,'2002-1-2 6:50:00'
insert into test select 10, 100,'2002-1-2 7:00:00'

insert into test select 10, 100,'2002-1-2 23:10:00'
insert into test select 10, 100,'2002-1-2 23:20:00'
insert into test select 10, 100,'2002-1-2 23:30:00'
insert into test select 10, 100,'2002-1-2 23:40:00'
insert into test select 10, 100,'2002-1-2 23:50:00'
insert into test select 10, 100,'2002-1-3 00:00:00'

insert into test select 10, 100,'2002-1-3 00:10:00'
insert into test select 10, 100,'2002-1-3 00:20:00'
insert into test select 10, 100,'2002-1-3 00:30:00'
insert into test select 10, 100,'2002-1-3 00:40:00'
insert into test select 10, 100,'2002-1-3 00:50:00'
insert into test select 10, 100,'2002-1-3 01:00:00'


insert into test select 10, 100,'2002-1-3 05:10:00'
insert into test select 10, 100,'2002-1-3 05:20:00'
insert into test select 10, 100,'2002-1-3 05:30:00'
insert into test select 10, 100,'2002-1-3 05:40:00'
insert into test select 10, 100,'2002-1-3 05:50:00'
insert into test select 10, 100,'2002-1-3 06:00:00'



select CONVERT(varchar(10),rq,120) as rq,SUM(sl) as sumsl from(
select case when 0<= DATEPART(HH,rq) and DATEPART(HH,rq)<6
then DATEADD(HH,-7,rq) else rq end as rq,sl from test)a
where CONVERT(varchar(10),rq,120) between '2002-01-01' and '2002-01-02'
group by CONVERT(varchar(10),rq,120)

/*
你的结果是这样:

2002-01-01 2401
2002-01-02 2405

正确的结果应该是:

2002-01-01 2400
2002-01-02 2405


*/


drop table test

------解决方案--------------------
SQL code
select convert(varchar(10),dateadd(mi,-370,rq),120) as rq,sum(sl) as sl from testwhere convert(varchar(5),rq,108)  not between '06:01' and '06:09'and convert(varchar(10),dateadd(mi,-370,rq),120) between '2002-01-01' and '2002-01-02'group by convert(varchar(10),dateadd(mi,-370,rq),120)/*rq         sl---------- -----------2002-01-01 24002002-01-02 2405(2 行受影响)*/