mysql - 按日期从当前每条记录中获取上一条记录

Question

我试图获取车辆在该特定日期加油时的先前加油里程。

我的表结构如下所示:

id      vehicle     center      dates           odometer        quantity
----------------------------------------------------------------------------
11      UAY329R     257         2017-04-01      153329          15  
12      S022        254         2017-03-22      139828          15  
13      UAP614Z     254         2017-04-01      211410          15  
14      S022        254         2017-03-25      139928          15  
15      UAY848Q     254         2017-04-01      239813          15  
16      C052        257         2017-04-11      15016           12.06   
17      UAP258A     254         2017-03-29      29495           20  
18      S022        254         2017-04-12      140078          35.66   
19      UAP258A     254         2017-04-01      29575           20  
20      UDL146E     254         2017-04-01      223701          20  
21      UAP258A     254         2017-04-03      29675           20  
22      UDL146E     254         2017-04-04      223851          5   

我正在尝试获得这样的输出:

prev_id     PrevOdo     CurrentId   CurrentOdo      Distance    Vehicle     Dates
----------------------------------------------------------------------------------------
8           139708          12      139828          120         S022        2017-03-22
12          139828          14      139928          100         S022        2017-03-25
12          139928          18      140078          150         S022        2017-04-12
10          29415           17      29495           80          UAP258A     2017-03-29
17          29495           19      29575           80          UAP258A     2017-04-01
19          29575           21      29675           100         UAP258A     2017-04-03
7           223601          20      223701          100         UDL146E     2017-04-01
20          223701          22      223851          150         UDL146E     2017-04-04

我的查询部分有效，但如果之前的交易中有多个记录，则第二个记录会显示不正确的 PrevOdo。

我的查询如下所示，请帮助我，提前谢谢您。

SELECT prev_id,  p.odometer AS 'PrevOdo', t.id AS 'CurrentId', t.odometer AS 'CurrentOdo', (t.odometer - p.odometer) AS 'Distance', t.vehicle AS 'Vehicle', t.dates AS 'Dates'
  FROM ( SELECT t.id, t.odometer, t.vehicle, t.dates, t.quantity, (
                    SELECT id
                      FROM ISSUANCE
                     WHERE dates < t.dates AND vehicle = t.vehicle
                     ORDER BY id DESC
                     LIMIT 1
                  ) prev_id
 FROM ISSUANCE t
 WHERE t.dates BETWEEN '2017-04-01' AND '2017-04-15')
 t LEFT JOIN ISSUANCE p ON t.prev_id = p.id
 ORDER BY t.vehicle

查询按日期过滤。

这是我使用的表结构，因为一些记录混合在一起，所以我无法遵循 id，我正在使用日期。提前致谢。

drop table if exists issuance;

create table issuance (
    id int,
    vehicle varchar(20),
    center int,
    dates date,
    odometer int,
    quantity int
);

insert into issuance values
 (02, 'S022', 254, '2017-04-14', 140178, 5),
 (04, 'S022', 254, '2017-04-18', 140378, 5),
 (11, 'UAY329R', 257, '2017-04-01', 153329, 15),
 (12, 'S022', 254, '2017-03-22', 139828, 15),
 (13, 'UAP614Z', 254, '2017-04-01', 211410, 15),
 (14, 'S022', 254, '2017-03-25', 139928, 15),
 (15, 'UAY848Q', 254, '2017-04-01', 239813, 15),
 (16, 'C052', 257, '2017-04-11', 15016 , 12.06),   
 (17, 'UAP258A', 254, '2017-03-29', 29495 , 20),
 (18, 'S022', 254, '2017-04-12', 140078, 35.66),   
 (19, 'UAP258A', 254, '2017-04-01', 29575 , 20),
 (20, 'UDL146E', 254, '2017-04-01', 223701, 20),
 (21, 'UAP258A', 254, '2017-04-03', 29675 , 20),
 (22, 'UDL146E', 254, '2017-04-04', 223851, 5),
 (23, 'S022', 254, '2017-03-26', 139948, 5),
 (25, 'S022', 254, '2017-04-16', 140278, 5),
 (27, 'S022', 254, '2017-04-19', 140478, 5),
 (98, 'S022', 254, '2017-04-22', 140578, 5);

select  prev.id as PrevId,
    prev.odometer as PrevOdo,
    cur.id as CurId,
    cur.odometer as CurOdo,
    cur.odometer - prev.odometer as distance,
    cur.vehicle as Vehicle,
    cur.dates as  Dates
 from    issuance cur
 join    (
        select  t1.id, max(t2.id) as prev_id
        from    issuance t1
        join    issuance t2
        on      t1.vehicle = t2.vehicle and
                t1.dates > t2.dates
        group by t1.id
    ) mid
 on      cur.id = mid.id
 join    issuance prev
 on      prev.id = mid.prev_id
 where   cur.dates between '2017-03-01' and '2017-05-15'
 ORDER BY Vehicle ASC, Dates ASC;

Answer 1

我将分两步为此构建一个查询:首先，我将获取 id/prev_id 对

select  t1.id, max(t2.id) as prev_id
from    issuance t1
join    issuance t2
on      t1.vehicle = t2.vehicle and
        t1.id > t2.id
group by t1.id

现在我将使用它作为原始表的自连接中的中间连接:

select  prev.id as PrevId,
        prev.odometer as PrevOdo,
        cur.id as CurId,
        cur.odometer as CurOdo,
        cur.odometer - prev.odometer as distance,
        cur.vehicle as Vehicle,
        cur.dates as  Dates
from    issuance cur
join    (
            select  t1.id, max(t2.id) as prev_id
            from    issuance t1
            join    issuance t2
            on      t1.vehicle = t2.vehicle and
                    t1.id > t2.id
            group by t1.id
        ) mid
on      cur.id = mid.id
join    issuance prev
on      prev.id = mid.prev_id
where   cur.dates between '2017-04-01' and '2017-04-15';

编辑

由于您不能依赖 ID 排序与日期排序保持一致，因此您必须更改加入标准；首先，您需要内部查询的最新日期而不是最大 id:

select  t1.id, max(t2.dates) as prev_dates
from    issuance t1
join    issuance t2
on      t1.vehicle = t2.vehicle and
        t1.dates > t2.dates
group by t1.id

那么这个表必须以不同的方式连接

select  prev.id as PrevId,
        prev.odometer as PrevOdo,
        cur.id as CurId,
        cur.odometer as CurOdo,
        cur.odometer - prev.odometer as distance,
        cur.vehicle as Vehicle,
        cur.dates as  Dates
from    issuance cur
join    (
            select  t1.id, max(t2.dates) as prev_dates
            from    issuance t1
            join    issuance t2
            on      t1.vehicle = t2.vehicle and
                    t1.dates > t2.dates
            group by t1.id
        ) mid
on      cur.id = mid.id
join    issuance prev
on      prev.dates = mid.prev_dates and
        prev.vehicle = cur.vehicle /* the mid calculated field is not sufficient anymore */
where   cur.dates between '2017-03-01' and '2017-05-15'
order by cur.vehicle asc, cur.dates asc

工作中的 rextester here