求一个简单的，我想了好久也没整明白

[zoujiaming]

我有一个表.
MeteTable

字段如下

nDate Temperature FileDate
2008-01-01 12:53 12.5 2008-01-01
2008-01-01 20:00 7.5 2008-01-01
2008-01-01 21:12 15.5 2008-01-01
2008-01-01 22:53 9.5 2008-01-01
2008-01-02 22:53 9.5 2008-01-02

过去，我通过GROUP BY FileDate，得到FileDate日期部分同的2008-01-01和2008-01-02，
现在问题是，如果nDate的值小时部分超过20点，就要计为下一天，比如
2008-01-01 21:12 15.5 2008-01-01

就要计为2008-01-02

也就是说通过查询上表的结果应该为
2008-01-01
2008-01-02(有超过20点)
2008-01-03(有超过20点)


请问如何解决，非常感谢

[zoujiaming]

对了，能否直接用sql语句解决？如果不能才用存储过程

[pt1314917]

--> 测试数据: @sMeteTable
declare @sMeteTable table (nDate datetime,Temperature numeric(3,1),FileDate datetime)
insert into @sMeteTable
select '2008-01-01 12:53',12.5,'2008-01-01' union all
select '2008-01-01 21:00',7.5,'2008-01-01' union all
select '2008-01-01 21:12',15.5,'2008-01-01' union all
select '2008-01-02 22:53',9.5,'2008-01-01' union all
select '2008-01-03 22:53',9.5,'2008-01-02'

select convert(varchar(10),ndate,120) from @sMeteTable where datepart(hh,ndate)>20 group by convert(varchar(10),ndate,120)

[水族杰纶]

set nocount on
declare @t table(nDate datetime,  Temperature float,    FileDate datetime)  
insert @t select '2008-01-01 12:53',        12.5 ,     '2008-01-01' 
insert @t select '2008-01-01 20:00',        7.5   ,     '2008-01-01' 
insert @t select '2008-01-01 21:12',        15.5 ,     '2008-01-01' 
insert @t select '2008-01-01 22:53',        9.5   ,     '2008-01-01' 
insert @t select '2008-01-02 22:53',        9.5   ,     '2008-01-02'
select filedate from (
select filedate=(case when datepart(hh,ndate)>20 then dateadd(dd,1,filedate)else filedate  end) from @t ) t group by filedate

/*
filedate                                               
------------------------------------------------------ 
2008-01-01 00:00:00.000
2008-01-02 00:00:00.000
2008-01-03 00:00:00.000

*/

[zoujiaming]

pt1314917大哥，我举的例子里，唯一差了一个RecorderID字段，为何我执行却不行呀


INSERT INTO METETABLE (nDate,RecorderID,IsCable,FileDate) values('2008-01-01 02:38','00022',1,'2008-01-01')
INSERT INTO METETABLE (nDate,RecorderID,IsCable,FileDate) values('2008-01-01 05:38','00022',1,'2008-01-01')
INSERT INTO METETABLE (nDate,RecorderID,IsCable,FileDate) values('2008-01-01 20:38','00022',1,'2008-01-01')
INSERT INTO METETABLE (nDate,RecorderID,IsCable,FileDate) values('2008-01-01 21:38','00022',1,'2008-01-01')
INSERT INTO METETABLE (nDate,RecorderID,IsCable,FileDate) values('2008-01-01 22:38','00022',1,'2008-01-01')

//SELECT FileDate FROM MeteTable Where RecorderID='00022' GROUP BY FileDate

select convert(varchar(10),ndate,120) from MeteTable
where RecorderID='00022' And datepart(hh,ndate)>20 group by convert(varchar(10),ndate,120)


我只得到了2008-01-01？

[幸运的意外]

select
filedate
from (select
nDate,Temperature,FileDate=(case when datepart(hh,ndate)>=20 then
dateadd(dd,1,filedate) else filedate end)
from @t ) t
group by
filedate
order by
filedate

[zoujiaming]

解决了，太谢谢两位大哥了，高人真多啊