增量业务数据表 查询繁琐,也要避免错误

/*
增量业务数据表
Id 自增id
BusId 业务主键
DataDate 增量数据所在的日期
*/
create table #room(Id int, BusId int, BusNo varchar(100), BusCode varchar(100), BusArea decimal(10,2), ts int, DataDate datetime)
-- 数据日期2018-09-01
INSERT INTO #room (Id, BusId, BusNo, BusCode, BusArea, ts, DataDate)
VALUES ('1', '40', 'xxxxxxxxx', 'yyyyy', '22.00', '1499639', '2018-09-01 17:39:24');
-- 数据日期2018-11-01
INSERT INTO #room (Id, BusId, BusNo, BusCode, BusArea, ts, DataDate)
VALUES ('2', '40', 'xxxxxxxxx', 'yyyyy', '24.00', '1499639', '2018-11-01 17:39:24');
-- 数据日期2018-10-01,发现10.1数据遗漏,补充.id变=3
INSERT INTO #room (Id, BusId, BusNo, BusCode, BusArea, ts, DataDate)
VALUES ('3', '40', 'xxxxxxxxx', 'yyyyy', '23.00', '1499639', '2018-10-01 17:39:24');

select * from #room
--查询2018-11-02日的最新数据

--现在实现版本,返回id=3的错误数据
select r.* from #room r join
(select max(id) id from #room where DataDate<='2018-11-02' group by BusId) rMax on rMax.id=r.Id

--数据正确版本,返回id=2的正确数据
-- 因为日期字段做条件会变慢,当时没有采取
-- 建议加一列 ImportTimeTS int,对应DataDate的ts.作为条件
select r.* from #room r join
(select max(DataDate) DataDate,BusId from #room where DataDate<='2018-11-02' group by BusId) rMax
on rMax.DataDate=r.DataDate and rMax.BusId=r.BusId