求一条按自定义日分组查询语句~~~~~~~~~~~~~~~~sos~~~~~~~~~~~~~

[yanghuigang]

表格式如下：
endtime no01
2010-1-1 12:00:00 0
2010-1-2 13:00:00 0
2010-1-6 12:30:00 0
2010-1-6 17:15:00 0
2010-1-6 18:15:00 0
2010-1-6 23:15:00 0
2010-1-7 11:30:00 0
2010-1-10 21:00:00 0
2010-1-10 21:30:00 0
2010-1-10 22:15:00 0
2010-1-10 23:30:00 0
2010-1-11 4:00:00 0
2010-1-11 4:30:00 0
2010-1-11 5:15:00 0
2010-1-11 6:15:00 0
2010-1-11 7:30:00 0
2010-1-11 7:45:00 0
2010-1-11 12:30:00 0
2010-1-11 13:15:00 0
2010-1-12 15:45:00 0
2010-1-13 12:15:00 0
: :
: :

需要自定义日为：当日08:00~次日08:00内的数据，将no01求和
求高手指点

[--小F--]

select
 sum(no01) 
from
 tb
where
  endtime  
between
 convert(varchar(10),getdate(),120)+' 08:00:00' 
and
 convert(varchar(10),dateadd(dd,1,getdate()),120)+' 08:00:00' 

[xuam]

select sum(no01) from Table where  endtime  between '2011-04-04 08:00:00' and '2011-04-05 08:00:00'

[bluesmiler]

select sum(no01) from tb
 where  endtime  
 between convert(nvarchar(10),getdate(),120)+' 08:00:00' 
 and convert(nvarchar(10),getdate()+1,120)+' 08:00:00' 

[yanghuigang]

[Quote=引用 2 楼 bluesmiler 的回复:]
SQL code
select sum(no01) from tb
where endtime
between convert(nvarchar(10),getdate(),120)+' 08:00:00'
and convert(nvarchar(10),getdate()+1,120)+' 08:00:00'
[/Quote]

可能我表述的不够清楚
我需要的数据为
2010-1-1 08:00:00 ~2010-1-2 08:00:00记为2010-1-1，然后sum(no01)
2010-1-2 08:00:00 ~2010-1-3 08:00:00记为2010-1-2，然后sum(no01)
2010-1-3 08:00:00 ~2010-1-4 08:00:00记为2010-1-3，然后sum(no01)

[yanghuigang]

是不是只能用UNION了啊，每一天都要union烦死个人，谁有简单一点的方法呢

[zhuoyuexl]

SELECT CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120),
sum(no01) from tb
group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)

[zhuoyuexl]

SELECT CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120),  
sum(no01) from tb
group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)

[偶然vs必然]

这个用个循环，查出一天的后，一个一个的用UNION ALL连接吧

[代码兔]

select LEFT(convert(varchar(30),dt,120),10) as d,SUM([no01]) as s from 
(
SELECT dateadd(hh,-8,[endtime]) as dt,[no01] --减8小时，将当天8点前的数额统计到前一天
  FROM tablename 
) b group by LEFT(convert(varchar(30),dt,120),10)
order by d

[liuweicai772]

SELECT COUNVERT(CHAR(10),GETDATE(),21) +'08:00:00'  AS BGRQ,
       ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ,
       SUM(NO01) AS ABC 
  FROM TB
   WHERE ENDTIME>= COUNVERT(CHAR(10，GETDATE(),21) +'08:00:00' AND 
         ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'

[liuweicai772]

SELECT CONVERT(CHAR(10),GETDATE(),21) +'08:00:00'  AS BGRQ,
       CONVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ,
       SUM(NO01) AS ABC 
  FROM TB
   WHERE ENDTIME>= COUNVERT(CHAR(10，GETDATE(),21) +'08:00:00' AND 
         ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'