mysql 从表中检索和操作当前行和上一行

Question

我已经看了好几个地方，也许我没有正确地表述搜索。我发现了类似的问题，但没有一个能回答问题。

我有一个即期库存表(用户在其中走过存储区域并实际检查手头有多少产品)。该表处理多个位置。表结构(部分，仅需要的信息)为:

create table location_inventory (
    id                int unsigned not null auto_increment,
    location_id       int unsigned references location(location_id),
    inventory_item_id int unsigned references inventory_item (inventory_item_id),
    inventory_date    date comment 'Date the sight inventory was taken',
    quantity          decimal( 15,2 ) comment 'Number of items found during inventory',
    primary key ( id ),
    unique            (location_id,inventory_item_id,inventory_date)
);

它应该是以下形式的查询:

select
    a.location_id location,
    a.inventory_item_id inventory_item,
    a.inventory_date curr_date,
    a.quantity curr_quant,
    b.inventory_date prev_date,
    b.quantity prev_quant,
    a.quantity - b.quantity num_used
from
    location_inventory a
    left outer join
       (
         select
            location_id,
            inventory_item_id,
            inventory_date,
            quantity
          from
            location_inventory
          where
           something
      ) b
      on ( location_id,inventory_item_id )
  where
      a.inventory_date between DATEA and DATEB

但我还没有让它工作。

我缺少的是整个子查询。我已经看到几个我们得到前一个日期的答案，但没有一个我可以从上一行中实际检索其余值的答案；它最终会检索整个表中最新条目的值。

Answer 1

当您选择只显示表结构的一部分时，您可以省略我们可能需要的东西。下面我假设您有一列 id 作为每一行的唯一标识符

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , b.inventory_date        prev_date
  , b.quantity              prev_quant
  , a.quantity - b.quantity num_used
FROM (
    SELECT
        *
      , (SELECT id
         FROM location_inventory
         WHERE location_id = t.location_id
           and inventory_item_id = t.inventory_item_id
           and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        ) prev_id
    FROM location_inventory t
    ) a
LEFT OUTER JOIN location_inventory b ON a.prev_id = b.id
WHERE a.inventory_date BETWEEN DATEA AND DATEB

另一种方法是对每个需要的值使用相关子查询:

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , (SELECT inventory_date
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )                   prev_date
  , (SELECT quantity
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )                   prev_quant
  , a.quantity 
    - (SELECT quantity
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )
FROM location_inventory t
WHERE a.inventory_date BETWEEN DATEA AND DATEB

自从 8.0 版本发布以来，MySQL 支持窗口函数，例如 lag()，这使得这变得更加容易和高效。

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , lag(inventory_date,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)  prev_date
  , lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)        prev_quant
  , a.quantity 
    - lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)       num_used
FROM location_inventory a
WHERE a.inventory_date BETWEEN DATEA AND DATEB

最后说明:我不赞成依赖于查询序列的别名方案