/*
增量業(yè)務數據表
Id 自增id
BusId 業(yè)務主鍵
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,發(fā)現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
