从表中选择最新数据

I have a table setup called item_histories, which is setup like below.

我有一个名为item_histories的表设置,其设置如下。

+--------------------------------+
|  id  itemId    date    price   |
+--------------------------------+
| 1    2    2015-01-10    310    |
| 2    2    2015-01-11    318    |
| 3    2    2015-01-12    326    |
| 4    2    2015-01-13    333    |
| 5    2    2015-01-14    335    |
| 6    6    2015-01-10    121598 |
| 7    6    2015-01-11    121598 |
| 8    6    2015-01-12    122666 |
| 9    6    2015-01-13    122666 |
| 10   6    2015-01-14    122666 |
+--------------------------------+

I'm trying to select the most recent row for each itemId, like so..

我正在尝试为每个itemId选择最近的行,就像这样。

+--------------------------------+
|  id  itemId    date    price   |
+--------------------------------+
| 5    2    2015-01-14    335    |
| 10   6    2015-01-14    122666 |
+--------------------------------+

I'm using Sequelize and this is the code I have now.

我正在使用Sequelize,这是我现在的代码。

db.itemHistory.findAll({
    attributes: [
        'id',
        'price',
        'itemId',
        [db.Sequelize.fn('max', db.Sequelize.col('date')), 'date']
    ],
    group: ['itemId'],
    limit: 1
}).then(function(histories) {
    console.log(histories);
});

Which generated this query, SELECT `id`, `price`, `itemId`, max(`date`) AS `test` FROM `item_histories` AS `itemHistory` GROUP BY `itemId` LIMIT 1.

其中生成了这个查询,SELECT`id`,`price`,`itemId`,max(`date`)AS`test` FROM`project_histories` AS`itemHistory` GROUP BY`itemId`LIMIT 1。

The MAX(`date`) was my attempt at accomplishing this, but all it does is select the first row, but has a date of the most recent.

MAX(`date`)是我尝试完成此操作,但它所做的只是选择第一行,但是具有最新的日期。

1 个解决方案

#1

If (itemId,date) is UNIQUE in item_histories, you can do something like this:

如果(itemId,date)在item_histories中是UNIQUE,你可以这样做:

 SELECT s.id
      , s.price
      , s.itemid
      , s.date
   FROM ( SELECT q.itemid
               , MAX(q.date) AS max_date
            FROM item_histories q
           GROUP BY q.itemid
        ) r
   JOIN item_histories s
     ON s.itemid = r.itemid  
    AND s.date   = r.max_date
  ORDER BY s.itemid

The inline view r returns the maximum value of date for each itemid. You can run that inline view query separately, to see (and confirm) what it returns.

内联视图r返回每个itemid的最大日期值。您可以单独运行该内联视图查询,以查看(并确认)它返回的内容。

We use the rows returned from that query as if it was a table. (In MySQL terminology, this is actually called a "derived table".)

我们使用从该查询返回的行,就好像它是一个表一样。 (在MySQL术语中,这实际上称为“派生表”。)

We perform a join operation back to the item_histories table, to get the rows that have a matching itemid and a date that matches the max_date returned from the inline view.

我们对item_histories表执行连接操作,以获取具有匹配itemid的行以及与从内联视图返回的max_date匹配的日期。

I noted at the beginning of the answer that this will work as long as (itemid,date) is UNIQUE in the table. If that isn't unique, we have the potential of returning multiple rows which have the same itemid and date.

我在回答的开头注意到,只要(itemid,date)在表中是UNIQUE,这将有效。如果这不是唯一的,我们有可能返回具有相同itemid和日期的多行。

(Sorry, I can't help you with whatever bizarre syntax Sequelize would need to generate that SQL statement. At the very least, Sequelize should provide some kind of "raw SQL" methods that will let you workaround the limitations.)

(对不起,我无法帮助你解决Sequelize生成SQL语句所需的任何奇怪的语法。至少,Sequelize应该提供某种“原始SQL”方法,让你解决这些限制。)

(There are other MySQL query patterns that can return an equivalent result.)

(还有其他MySQL查询模式可以返回相同的结果。)

FOLLOWUP

To add a join to items table (assuming that itemid is a foreign key referencing item(id)

向项目表添加联接(假设itemid是外键引用项(id)

Repeating the same query from above, the changes (added lines) are between the comment dash lines.

从上面重复相同的查询,更改(添加的行)位于注释虚线之间。

 SELECT s.id
      , s.price
      , s.itemid
      , s.date
 -- ---------------------------------  
      , t.id      AS t_id
      , t.col2    AS t_col2
 -- ---------------------------------   
   FROM ( SELECT q.itemid
               , MAX(q.date) AS max_date
            FROM item_histories q
           GROUP BY q.itemid
        ) r
   JOIN item_histories s
     ON s.itemid = r.itemid  
    AND s.date   = r.max_date
 -- ---------------------------------  
   JOIN items t
     ON t.id = r.itemid
 -- ---------------------------------  
  ORDER BY s.itemid

#1