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

Question

我看过几个地方，也许我只是没有正确地措辞搜索。我发现了类似的问题，但没有一个回答这个问题。

我有一张 Sight Inventories 表（用户在该表中走过存储区并实际检查手头有多少产品）。该表处理多个位置。表结构（部分，仅需要的信息）为：

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

但我还没有让它工作。

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

标签： mysqlself-joinmysql-dependent-subquery

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

最后一点：我不赞成依赖于查询序列的别名方案