如下: select a.* from table1 as a, (select tp, convert(char(13),tm,120) as tm1, max(tm) as tm from table1 group by tp,tm1) as b where a.tp=b.tp and a.tm=b.tm没有测试,可能有误,供你参考。
我写一个错误的SQL大家就看得出来! select tp,max(tm),vl(这个值为max TM时的值!) from 表 where tm<='2002-3-3 20:00' group by tp
再来个效率低的! 创造一个视图:多一个整点字段 select tp,tm,tl,整点tm(用Convert作),(tm-整点tm) as diffmin from table1而后select 整点tm,min(diffmin) group by 整点tm而后在找出diffmin一样的,可以用临时表。反正是个思路,只是效率很差!
类别 时间 值 (tp char) (tm datetime) (vl numeric) CGD020023 2002-3-21 23:51:35 305.539 CGD020023 2002-3-21 23:56:59 544.495 (*) CGD020023 2002-3-22 0:02:24 604.161 CGD020023 2002-3-22 0:50:59 215.099 CGD020023 2002-3-22 0:56:21 125.712 (*) CGD020023 2002-3-22 1:01:44 510.013 CGD020023 2002-3-22 1:07:08 382.672 CGD020023 2002-3-22 1:55:40 361.694 (*) CGD020023 2002-3-22 2:00:04 5.511 (*) CGD020024 2002-3-21 23:51:35 319.784 CGD020024 2002-3-21 23:56:59 45.972 (*) CGD020024 2002-3-22 0:02:24 299.963 CGD020024 2002-3-22 0:50:59 641.163 CGD020024 2002-3-22 0:56:21 531.216 (*) CGD020024 2002-3-22 1:00:44 442.937 CGD020024 2002-3-22 1:07:08 487.65 CGD020024 2002-3-22 1:55:40 479.904 (*) CGD020024 2002-3-22 2:01:04 327.103 (*) MS SQL 2000: select test.tp,test.tm,test.vl from test join ( select tp,floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 as a, min(floor(24*(cast (tm as float)-floor(cast(tm as float))))+1- 24*(cast (tm as float)-floor(cast(tm as float)))) as c from test group by tp, floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 ) t1 on test.tp=t1.tp and floor(24*(cast (tm as float)-floor(cast(tm as float))))+1- 24*(cast (tm as float)-floor(cast(tm as float)))=t1.c
正确的应该是这个!你试一下Select * from 表, ( select tp,MAX(tm) as MaxTm from 表 group by tp, cast(Year(tm),char(4))+'-'+cast(Month(tm),char(4)) +'-'+cast(Days(tm),char(4)+' '+cast(Hour(tm),char(4))+':00:00' ) as 表1 Where 表.tm=表1.MaxTm And 表.tp=表1.
select tp,max(tm),vl(这个值为max TM时的值!) from 表 where tm<='2002-3-3 20:00' and tm>'2002-3-3 19:00' group by tp
上面的语句没错,不过为了调试方便多加了一个字段,改一下: select test.tp,test.tm,test.vl from test join ( select tp,min(floor(24*(cast (tm as float)-floor(cast(tm as float))))+1- 24*(cast (tm as float)-floor(cast(tm as float)))) as c from test group by tp, floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 ) t1 on test.tp=t1.tp and floor(24*(cast (tm as float)-floor(cast(tm as float))))+1- 24*(cast (tm as float)-floor(cast(tm as float)))=t1.c order by test.tp,test.tm
jamex(1 + 1 = 爱) 的思路完全正确,但好象没有HOUR函数:Select * from 表, ( select tp,MAX(tm) as MaxTm from 表 group by tp, cast(Year(tm),char(4))+cast(Month(tm),char(2)) +cast(Day(tm),char(2)+' '+cast(datepart(hour,tm),char(2)) ) as 表1 Where 表.tm=表1.MaxTm And 表.tp=表1.tp
to :jamex(1 + 1 = 爱) 大哥这个思路是正确的,只是不要精确到秒上可不可以,只要到分钟也就是如果2002-3-3 2:00:45有数据的话,就只要这条了!!而不要2002-3-3 1:59:59的数据!
你试一下 icevi(按钮工厂) 的方法,我还没看懂。
to:icevi(按钮工厂) 大哥你是把时间转为数值来做,好像不行啊!!会把时间没掉一截的!
cast(Year(tm),char(4))+'-'+cast(Month(tm),char(4)) +'-'+cast(Days(tm),char(4)+' '+cast(Hour(tm),char(4))+':00:00' 中的":00:00"没有意义。 为什么不用 convert(char(13),tm,120) as tm1, max(tm) ??
select tp,tm from tablename a1 (select tp,max(tm) as tm from (select tp,tm,(convert(char(10),tm,120)+' '+CONVERT(CHAR(2),datepart(hour,tm)+1)) as zdsj where datepart(minute,tm)>0 union select tp,tm,(convert(char(10),tm,120)+' '+CONVERT(CHAR(2),datepart(hour,tm)+1)) as zdsj where datepart(minute,tm)=0) a group by a.tp,a.zdsj) b on a1.tp=b.tp and a1.tm=b.tm
select tp,tm,(convert(char(10),tm,120)+' '+CONVERT(CHAR(2),datepart(hour,tm)+1)) as zdsj where datepart(minute,tm)=0) a group by a.tp,a.zdsj) b on a1.tp=b.tp and a1.tm=b.tm 上面写错了。 应该等于0的地方不要加1。 我的思路是这样的,其实和 8992026(8992026的思路是一样的。 先把分钟等于0的取日期和小时数,不等于0取日期和小时数+1连接起来作为整点数字段。 得到这样的结果 tp tm zdsj a 2001-01-01 02:00:00 2001-01-01 2 a 2001-01-01 02:01:00 2001-01-01 3 a 2001-01-01 02:45:00 2001-01-01 3然后group by tp,zdsj,并把最大的tm取出来好了。上面的语句我没有调试过。不对,你再改 一下。
TO zcflion(土匪) :转成数值只是为了生成对应的整点值,好进行分组统计。我上面的语句还有是点问题(应该按tp及日期来分组),而且若按你说的“不要精确到秒上,只精确到分钟上”就又要改了:(。前面几位的方法不错。不过怎么都把 cast(Year(tm) as char(4)) 写成 cast(Year(tm),char(4)) 了?select test.* from test join ( select tp,max(tm) as maxtm from test group by tp, convert(varchar,tm,105), ceiling(cast(substring(convert(varchar,tm,108),4,2) as numeric)/100+ cast(left(convert(varchar,tm,108),2) as numeric)) ) t1 on test.tp=t1.tp and test.tm=t1.maxtm另:不要再叫我大哥:)
看看下面的这个可行?select a.* from table1 as a, (select tp, convert(char(13),DATEADD(mi,1,tm),120) as tm1, max(tm) as tm from table1 group by tp,tm1) as b where a.tp=b.tp and a.tm=b.tm
DATEADD(mi,1,tm) ==> DATEADD(mi,-1,tm)
to zcflion(土匪):回复人: zcflion(土匪) ( ) 信誉:100 2002-3-26 14:06:19 得分:0 to :jamex(1 + 1 = 爱) 大哥这个思路是正确的,只是不要精确到秒上可不可以,只要到分钟 也就是如果2002-3-3 2:00:45有数据的话,就只要这条了!! 而不要2002-3-3 1:59:59的数据! 再试试这个!Select * from 表, ( select tp,MAX(tm) as MaxTm from 表 group by tp, cast(Year(DATEADD(mi,-1,tm)),char(4))+cast(Month(DATEADD(mi,-1,tm)),char(2)) +cast(Day(DATEADD(mi,-1,tm)),char(2)+' '+cast(datepart(hour,DATEADD(mi,-1,tm)),char(2)) ) as 表1 Where 表.tm=表1.MaxTm And 表.tp=表1.tp
一句sql 好象出不来:Select tp, Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2)) +'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00' ) As tm1, Min(DateDiff(Second, Cast(Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2)) +'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00' ), DateTime), tm) As dif Into 临时表 From tablename Group By tp, Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2)) +'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00' )Select t.* From tablename t, 临时表 Where t.tp = 临时表.tp And Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2)) +'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00' ) = 临时表.tm1 And DateDiff(Second, Cast(Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2)) +'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00' ) = 临时表.dif
Select tbldata.* from tbldata, ( select tp, MAX(tm) as MaxTm from tbldata group by tp, ltrim(rtrim(cast(Year(DATEADD(mi,-1,tm)) as char(4)))) +right('00'+ltrim(rtrim(cast(Month(DATEADD(mi,-1,tm)) as char(2)))),2) +right('00'+ltrim(rtrim(cast(Day(DATEADD(mi,-1,tm)) as char(2)))),2) +right('00'+ltrim(rtrim(cast(datepart(hour,DATEADD(mi,-1,tm)) as char(2)))),2)) as tbldata1 Where tbldata.tm=tbldata1.MaxTm And tbldata.tp=tbldata1.tp order by tblData.tp,tblData.tm
我给一个可运行的语句:select a.* from table1 as a, ( select tp,tm1,max(tm) as tm from (select tp, convert(char(13),DATEADD(mi,-1,tm),120) as tm1, tm from table1) as c group by tp,tm1) as b where a.tp=b.tp and a.tm=b.tm
这个是最终的结果!!select T.* from st_river_r0_t T, (select stcd,max(ymdhm) ymdhm from st_river_r0_t group by stcd,convert(char(8),DATEADD(second, -1,ymdhm),112), datepart(hour,DATEADD(second, -1,ymdhm))) T1 where T.stcd=T1.stcd and T.ymdhm=T1.ymdhm我在大家的帮助下终于搞定了!谢谢大家!呵呵开心!!呆会儿结贴!!不过可惜的是,我弄了这么久老板现在说,不用做了数据库是空的!!??吐血!!!
select a.*
from table1 as a,
(select tp, convert(char(13),tm,120) as tm1, max(tm) as tm from table1 group by tp,tm1) as b
where a.tp=b.tp and a.tm=b.tm没有测试,可能有误,供你参考。
select tp,max(tm),vl(这个值为max TM时的值!)
from 表
where tm<='2002-3-3 20:00'
group by tp
创造一个视图:多一个整点字段
select tp,tm,tl,整点tm(用Convert作),(tm-整点tm) as diffmin
from table1而后select 整点tm,min(diffmin) group by 整点tm而后在找出diffmin一样的,可以用临时表。反正是个思路,只是效率很差!
CGD020024 2002-3-22 1:55:40 479.904 (*)
CGD020024 2002-3-22 2:01:04 327.103 为什么取第二条呢。如果是每个整天取一条的话,并且往前推的话,那么1点多往2点靠。2点是整天。2点往3点靠。
那么得到的结果应该是第二条,整点是两点。还有第三条,整点是3点。但你为什么要选第二条,而第三条不要呢。
CGD020023 2002-3-22 1:07:08 382.672
CGD020023 2002-3-22 1:55:40 361.694
CGD020023 2002-3-22 2:00:04 5.511 (*)
就像你上面这些结果。都应该大于1点的和等于2:00都算做两点这个整点组。那么如果再来一条记录2:01:00,也是23日。要不要呢。
结果应该是下面带星号的记录:
类别 时间 值
(tp char) (tm datetime) (vl numeric)
CGD020023 2002-3-21 23:51:35 305.539
CGD020023 2002-3-21 23:56:59 544.495 (*)
CGD020023 2002-3-22 0:02:24 604.161
CGD020023 2002-3-22 0:50:59 215.099
CGD020023 2002-3-22 0:56:21 125.712 (*)
CGD020023 2002-3-22 1:01:44 510.013
CGD020023 2002-3-22 1:07:08 382.672
CGD020023 2002-3-22 1:55:40 361.694 (*)
CGD020023 2002-3-22 2:00:04 5.511 (*)
CGD020024 2002-3-21 23:51:35 319.784
CGD020024 2002-3-21 23:56:59 45.972 (*)
CGD020024 2002-3-22 0:02:24 299.963
CGD020024 2002-3-22 0:50:59 641.163
CGD020024 2002-3-22 0:56:21 531.216 (*)
CGD020024 2002-3-22 1:00:44 442.937
CGD020024 2002-3-22 1:07:08 487.65
CGD020024 2002-3-22 1:55:40 479.904 (*)
CGD020024 2002-3-22 2:01:04 327.103 (*) MS SQL 2000:
select test.tp,test.tm,test.vl
from test join (
select tp,floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 as a,
min(floor(24*(cast (tm as float)-floor(cast(tm as float))))+1-
24*(cast (tm as float)-floor(cast(tm as float)))) as c
from test
group by tp, floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 ) t1
on test.tp=t1.tp and
floor(24*(cast (tm as float)-floor(cast(tm as float))))+1-
24*(cast (tm as float)-floor(cast(tm as float)))=t1.c
(
select tp,MAX(tm) as MaxTm
from 表
group by tp, cast(Year(tm),char(4))+'-'+cast(Month(tm),char(4))
+'-'+cast(Days(tm),char(4)+' '+cast(Hour(tm),char(4))+':00:00'
) as 表1
Where 表.tm=表1.MaxTm And 表.tp=表1.
from 表
where tm<='2002-3-3 20:00' and tm>'2002-3-3 19:00'
group by tp
select test.tp,test.tm,test.vl
from test join (
select tp,min(floor(24*(cast (tm as float)-floor(cast(tm as float))))+1-
24*(cast (tm as float)-floor(cast(tm as float)))) as c
from test
group by tp, floor(24*(cast (tm as float)-floor(cast(tm as float))))+1 ) t1
on test.tp=t1.tp and
floor(24*(cast (tm as float)-floor(cast(tm as float))))+1-
24*(cast (tm as float)-floor(cast(tm as float)))=t1.c
order by test.tp,test.tm
(
select tp,MAX(tm) as MaxTm
from 表
group by tp, cast(Year(tm),char(4))+cast(Month(tm),char(2))
+cast(Day(tm),char(2)+' '+cast(datepart(hour,tm),char(2))
) as 表1
Where 表.tm=表1.MaxTm And 表.tp=表1.tp
+'-'+cast(Days(tm),char(4)+' '+cast(Hour(tm),char(4))+':00:00'
中的":00:00"没有意义。
为什么不用 convert(char(13),tm,120) as tm1, max(tm) ??
cast(Year(tm),char(4))+'-'+cast(Month(tm),char(2))
+'-'+cast(Days(tm),char(2)+' '+cast(Hour(tm),char(2))
应该写成
convert(char(13),tm,120)
(select tp,max(tm) as tm from
(select tp,tm,(convert(char(10),tm,120)+' '+CONVERT(CHAR(2),datepart(hour,tm)+1)) as zdsj where datepart(minute,tm)>0
union
select tp,tm,(convert(char(10),tm,120)+' '+CONVERT(CHAR(2),datepart(hour,tm)+1)) as zdsj where datepart(minute,tm)=0) a group by a.tp,a.zdsj) b on a1.tp=b.tp and a1.tm=b.tm
上面写错了。 应该等于0的地方不要加1。
我的思路是这样的,其实和 8992026(8992026的思路是一样的。
先把分钟等于0的取日期和小时数,不等于0取日期和小时数+1连接起来作为整点数字段。
得到这样的结果
tp tm zdsj
a 2001-01-01 02:00:00 2001-01-01 2
a 2001-01-01 02:01:00 2001-01-01 3
a 2001-01-01 02:45:00 2001-01-01 3然后group by tp,zdsj,并把最大的tm取出来好了。上面的语句我没有调试过。不对,你再改 一下。
from test join (
select tp,max(tm) as maxtm
from test
group by tp, convert(varchar,tm,105),
ceiling(cast(substring(convert(varchar,tm,108),4,2) as numeric)/100+
cast(left(convert(varchar,tm,108),2) as numeric))
) t1
on test.tp=t1.tp and test.tm=t1.maxtm另:不要再叫我大哥:)
1. 按liujianjun_(流星尔) 所说,取2个集合。
a,如果有整点(按分取整后), 取整点的记录;
b.如果没有整点, 取小于整点的最大记录
2 按分取整后减1分钟, 取小于整点的最大记录
from table1 as a,
(select tp, convert(char(13),DATEADD(mi,1,tm),120) as tm1, max(tm) as tm from table1 group by tp,tm1) as b
where a.tp=b.tp and a.tm=b.tm
to :jamex(1 + 1 = 爱) 大哥这个思路是正确的,只是不要精确到秒上可不可以,只要到分钟
也就是如果2002-3-3 2:00:45有数据的话,就只要这条了!!
而不要2002-3-3 1:59:59的数据!
再试试这个!Select * from 表,
(
select tp,MAX(tm) as MaxTm
from 表
group by tp, cast(Year(DATEADD(mi,-1,tm)),char(4))+cast(Month(DATEADD(mi,-1,tm)),char(2))
+cast(Day(DATEADD(mi,-1,tm)),char(2)+' '+cast(datepart(hour,DATEADD(mi,-1,tm)),char(2))
) as 表1
Where 表.tm=表1.MaxTm And 表.tp=表1.tp
+'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00'
) As tm1, Min(DateDiff(Second, Cast(Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2))
+'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00'
), DateTime), tm) As dif
Into 临时表
From tablename
Group By tp, Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2))
+'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00'
)Select t.*
From tablename t, 临时表
Where t.tp = 临时表.tp
And Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2))
+'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00'
) = 临时表.tm1
And DateDiff(Second, Cast(Cast(Year(tm),Char(4))+'-'+Cast(Month(tm),char(2))
+'-'+Cast(Days(tm),char(2)+' '+Cast(Hour(tm),Char(2))+':00:00'
) = 临时表.dif
假设表名为tbldata
Select tbldata.* from tbldata,
(
select tp,
MAX(tm) as MaxTm
from tbldata
group by tp,
ltrim(rtrim(cast(Year(DATEADD(mi,-1,tm)) as char(4))))
+right('00'+ltrim(rtrim(cast(Month(DATEADD(mi,-1,tm)) as char(2)))),2)
+right('00'+ltrim(rtrim(cast(Day(DATEADD(mi,-1,tm)) as char(2)))),2)
+right('00'+ltrim(rtrim(cast(datepart(hour,DATEADD(mi,-1,tm)) as char(2)))),2)) as tbldata1
Where tbldata.tm=tbldata1.MaxTm And tbldata.tp=tbldata1.tp
order by tblData.tp,tblData.tm
from table1 as a,
( select tp,tm1,max(tm) as tm
from (select tp, convert(char(13),DATEADD(mi,-1,tm),120) as tm1, tm from table1) as c
group by tp,tm1) as b
where a.tp=b.tp and a.tm=b.tm
(select stcd,max(ymdhm) ymdhm from st_river_r0_t
group by stcd,convert(char(8),DATEADD(second, -1,ymdhm),112),
datepart(hour,DATEADD(second, -1,ymdhm))) T1
where T.stcd=T1.stcd and T.ymdhm=T1.ymdhm我在大家的帮助下终于搞定了!谢谢大家!呵呵开心!!呆会儿结贴!!不过可惜的是,我弄了这么久老板现在说,不用做了数据库是空的!!??吐血!!!