如何查询每天8点前的最后一条记录?
电表读数问题，电表每天按固定时间间隔读取数据，报表要求提取出每天8点前的最后一条记录做为前一天的读数，求教SQL语句怎么写?
[解决办法]
select
b.*
from
(select distinct(convert(char(10),日期,120))+ ' 08:00:00 ' as 日期 from 表) a,
表 b
where
a.日期> b.日期
and
not exists(select 1 from 表 where 日期 <a.日期 and 日期> b.日期)
[解决办法]
select top 1 * from table_name
where convert(varchar(10), time_column, 112) < convert(varchar(8), getdate(), 112) + '08 '
order by time_column desc
[解决办法]
select top 1 * from table_name
where convert(varchar(13), time_column, 120) < convert(varchar(11), getdate(), 120) + '08 '
order by time_column desc
[解决办法]
Create Table TEST(ID Int, TestTime DateTime)
Insert TEST Select 1, '2007-03-29 07:30:00 '
Union All Select 2, '2007-03-29 08:00:00 '
Union All Select 3, '2007-03-30 07:00:00 '
Union All Select 4, '2007-03-31 07:00:00 '
Union All Select 5, '2007-03-31 07:50:00 '
GO
Select * From TEST A
Where Not Exists(Select 1 From TEST Where DateDiff(dd, TestTime, A.TestTime) = 0 And TestTime > A.TestTime And DatePart(Hour, TestTime) < 8)
And DatePart(Hour, TestTime) < 8
GO
Drop Table TEST
--Result
/*
IDTestTime
12007-03-29 07:30:00.000
32007-03-30 07:00:00.000
52007-03-31 07:50:00.000
*/
[解决办法]
select top 1 * from table_name
where convert(varchar(10), time_column, 121) < convert(varchar(7), getdate(), 121) + '-08 '
order by time_column desc
[解决办法]
Create Table TEST(ID Int, TestTime DateTime)
Insert TEST Select 1, '2007-03-19 07:30:00 '
Union All Select 2, '2007-03-19 08:00:00 '
Union All Select 3, '2007-03-20 07:00:00 '
Union All Select 4, '2007-03-21 07:00:00 '
Union All Select 5, '2007-03-21 07:50:00 '
Union All Select 6, '2007-03-22 18:08:00 '
Union All Select 7, '2007-03-23 08:00:00 '
GO
Select * From TEST A
Where Not Exists(Select 1 From TEST
Where (DateDiff(dd, TestTime, A.TestTime) = 0 And TestTime > A.TestTime And DatePart(Hour, TestTime) <= 8)
or
(DateDiff(dd, TestTime, A.TestTime) = 1 And TestTime < A.TestTime And DatePart(Hour, TestTime) > 8)
)
GO
drop table test
go
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,dt datetime,value int)
insert into tb(id,dt,value) values(1, '2007-03-01 01:00:00 ', 1)
insert into tb(id,dt,value) values(1, '2007-03-01 02:00:00 ', 2)
insert into tb(id,dt,value) values(1, '2007-03-01 03:00:00 ', 3)
insert into tb(id,dt,value) values(1, '2007-03-01 04:00:00 ', 4)
insert into tb(id,dt,value) values(1, '2007-03-01 05:00:00 ', 5)
insert into tb(id,dt,value) values(2, '2007-03-01 06:00:00 ', 6)
insert into tb(id,dt,value) values(2, '2007-03-01 07:00:00 ', 7)
insert into tb(id,dt,value) values(2, '2007-03-01 08:00:00 ', 8)
insert into tb(id,dt,value) values(2, '2007-03-01 09:00:00 ', 9)
insert into tb(id,dt,value) values(2, '2007-03-01 10:00:00 ', 10)
insert into tb(id,dt,value) values(1, '2007-03-02 01:00:00 ', 1)
insert into tb(id,dt,value) values(1, '2007-03-02 02:00:00 ', 2)
insert into tb(id,dt,value) values(1, '2007-03-02 03:00:00 ', 3)
insert into tb(id,dt,value) values(1, '2007-03-02 04:00:00 ', 4)
insert into tb(id,dt,value) values(1, '2007-03-02 05:00:00 ', 15)
insert into tb(id,dt,value) values(2, '2007-03-02 06:00:00 ', 6)
insert into tb(id,dt,value) values(2, '2007-03-02 07:00:00 ', 7)
insert into tb(id,dt,value) values(2, '2007-03-02 08:00:00 ', 8)
insert into tb(id,dt,value) values(2, '2007-03-02 09:00:00 ', 9)
insert into tb(id,dt,value) values(2, '2007-03-02 10:00:00 ', 20)
go
select a.* from tb a,
(
select id , convert(varchar(10),dt,120) rq, max(dt) dt
from tb
where right(convert(varchar(13),dt,120),2) < '08 '
group by id , convert(varchar(10),dt,120)
) b
where a.id = b.id and convert(varchar(10),a.dt,120) = b.rq and a.dt = b.dt
order by a.id,a.dt
drop table tb
/*
id dt value
----------- ------------------------------------------------------ -----------
1 2007-03-01 05:00:00.000 5
1 2007-03-02 05:00:00.000 15
2 2007-03-01 07:00:00.000 7
2 2007-03-02 07:00:00.000 7
（所影响的行数为 4 行）
*/
[解决办法]
select 'userid ',max( 'timecolumn ') from 'tablename ' group by 'userid ' where timecolumn <8点
[解决办法]
表tb中加入一个计算字段，为日期字段－8小时
alter table tb add dt2 as DATEADD(hh,-8,dt)
求出每天最后一次记录时间（已偏移），在这个范围找整个记录
select * from tb where dt2 in
(
select max(dt2) from tb
group by convert(varchar(10),dt2,120)
)
[解决办法]
使用group就可以了，分别统计每天八点的最后一条记录，
select max(time) from a where f1(time) <=8 group by f2(time)
关键就在这个f1,f2函数，f1函数的作用是将8点以后的数据过滤掉；f2函数的作用就是将每天的数据分为一类，所以可以这样设计f1(time)=hour(time),f2=convert(time, "yyyymmdd ")
显然，一个是取小时，一个是去日期，具体看你使用什么数据库，用相应的函数实现即可
[解决办法]
declare @t Table (ID Int, TestTime DateTime)
Insert @t Select 1, '2007-03-19 07:30:00 '
Union All Select 2, '2007-03-19 08:00:00 '
Union All Select 3, '2007-03-20 07:00:00 '
Union All Select 4, '2007-03-21 07:00:00 '
Union All Select 5, '2007-03-21 07:50:00 '
Union All Select 6, '2007-03-22 18:08:00 '
Union All Select 7, '2007-03-23 08:00:00 '
select max(TestTime) from @t group by datediff(day, dateadd(Hour,-8,TestTime),0)
[解决办法]
Select * From 电能表采集 A
Where Not Exists(Select 1 From 电能表采集 Where DateDiff(dd, 电表时间, A.电表时间) = 0 And 电表时间 > A.电表时间 And DatePart(Hour, 电表时间) < 8)
And DatePart(Hour, 电表时间) < 8
-------
看你的語句，稍微修改下上面的語句，這個應該可以吧,
------解决方案--------------------
select * from sajet.g_sn_travel a
where not exists(
select * from sajet.g_sn_travel b where b.out_process_time> a.out_process_time and a.serial_number=b.serial_number )
and out_process_time < to_date(to_char(sysdate, 'YYYYMMDD ')|| '08 ', 'YYYYMMDDHH24 ');
可以参照这个
sajet.g_sn_travel 表名
serial_number,列名（类似用户名）
