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

xesrikrc 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(493)

我看了好几个地方，也许我只是没有正确的措辞搜索。我发现了类似的问题，但没有一个能回答这个问题。
我有一个目测库存表（在这里，用户通过存储区，实际检查有多少产品在手）。该表处理多个位置。表格结构（部分，仅需要信息）为：

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

但我还没能成功。
我遗漏的就是整个子查询。我已经看到了几个答案，其中我们得到了上一个日期，但没有一个我可以从上一行实际检索其余的值；它最终检索整个表中最近一个条目的值。

mysql self-join mysql-dependent-subquery

来源：https://stackoverflow.com/questions/52488368/mysql-retrieve-and-manipulate-current-row-and-previous-row-from-table

1条答案

按热度按时间

anauzrmj1#

当您选择只显示表结构的一部分时，可以忽略我们可能需要的内容。下面我假设你有一个专栏 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

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

赞(0）回复(0）举报 2021-06-18

我来回答

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

1条答案

相关问题

热门标签

最新问答