mysql - 获取 View 中表的最新

Question

我正在尝试创建一个 View ，该 View 获取每个项目的最新历史记录并将其连续返回。我要运行的查询如下所示:

SELECT item_pk.Id Id, item_name.Value Name 
FROM item_pk
JOIN 
(
    SELECT * FROM item_name
    ORDER BY Occurred DESC
) item_name ON item_name.Id = item_pk.Id
GROUP BY item_pk.Id

此查询工作正常并返回我想要的内容，但是当我尝试将其转换为 View 时，它不允许我使用子查询。当我尝试时:

CREATE VIEW item_name_latest AS SELECT * FROM item_name ORDER BY Occurred DESC;
CREATE VIEW item AS SELECT item_pk.Id Id, item_name.value Name 
FROM item_pk
JOIN item_name_latest item_name ON item_name.Id = item_pk.Id
GROUP BY item_pk.Id

item View 产生了错误的结果。我得到的结果与我直接加入 item_name 而没有产生正确顺序的中间 View 完全相同。也就是说，我不是从 item_name 接收每项的最新行，而是接收最旧的行。直接调用 SELECT 语句(而不是在 View 上)也会产生错误的结果。

假设我有以下数据:

Id | Value        | Occurred
1    Bob            1325697408000
1    Bobbie         1325697536000
2    Stuff          1325697822000
2    More Stuff     1325697823000
2    Latest Stuff   1325697824000
1    Roberta        1325697945000

预期结果(以及给定查询和子查询的实际结果)是:

1    Roberta
2    Latest Stuff

实际的 View 结果是:

1    Bob
2    Stuff

有没有办法用 View 生成预期值，或者有更好的方法来获取这些值？

Answer 1

您希望它创建您的 View :

create view item_latest as
select
  id, 
    max(occurred) as latestoccurred 
from 
    item_name 
group by
    id;

create view item as
select
    i1.id,
    i1.value
from
    item_name i1
    inner join item_latest i2 on
        i1.id = i2.id
        and i1.occurred = i2.latestoccurred;

它只抓取那些 occurred 值为 id 的最大 occurred 值的项目。