选择具有最近日期的行

Sample table data:

样本表数据：

+---------+--------+-------+---------------------+----------+
|   ref   |  cost  | stock |        date         | location |
+---------+--------+-------+---------------------+----------+
| 00AD075 | 352.33 |     6 | 2017-06-14 08:24:43 | WA       |
| 00AD075 | 352.33 |     4 | 2017-06-19 06:01:14 | WA       |
| 00AD075 | 352.33 |     0 | 2017-06-22 00:00:00 | WA       |
| 00AE912 | 231.46 |     2 | 2017-06-14 08:24:43 | WA       |
| 00AE912 | 231.46 |     1 | 2017-06-15 06:31:03 | WA       |
| 00AE912 | 231.46 |     2 | 2017-06-16 06:01:16 | NY       |
| 00AE912 | 231.46 |     0 | 2017-06-19 00:00:00 | WA       |
| 00AE938 | 852.25 |     2 | 2017-06-14 08:24:43 | NY       |
| 00AG510 | 172.27 |     1 | 2017-06-14 08:24:43 | NY       |
| 00AG520 | 359.67 |     6 | 2017-06-14 08:24:43 | NY       |
| 00AG590 | 747.21 |     1 | 2017-06-14 08:24:43 | CA       |
| 00AG590 | 550.00 |     1 | 2017-06-15 06:31:03 | CA       |
+---------+--------+-------+---------------------+----------+

pseduo:

pseduo：

fetch all where ref LIKE '00%' AND stock > 0
获取所有ref LIKE '00％'和stock> 0的地方
order by date DESC
按日期排序DESC
Keep the most recent and discard all other rows with same location
保持最新并丢弃具有相同位置的所有其他行
if stock not greater than 0 discard it too
如果库存不大于0也丢弃它

Expected data:

预期数据：

+----------+----------+---------+-----------------------+-----+
| 00AD075  |  352.33  |      4  |  2017-06-19 06:01:14  |  WA |
+----------+----------+---------+-----------------------+-----+
| 00AE912  |  231.46  |      2  |  2017-06-16 06:01:16  |  NY |
| 00AE938  |  852.25  |      2  |  2017-06-14 08:24:43  |  NY |
| 00AG510  |  172.27  |      1  |  2017-06-14 08:24:43  |  NY |
| 00AG520  |  359.67  |      6  |  2017-06-14 08:24:43  |  NY |
| 00AG590  |  550.00  |      1  |  2017-06-15 06:31:03  |  CA |
+----------+----------+---------+-----------------------+-----+

So there are multiple rows for each ref, based on the date. I need to select the most recent row. This is what I'm doing, but it gives me an error:

因此，根据日期，每个ref都有多行。我需要选择最近的一行。这就是我正在做的，但它给了我一个错误：

SELECT DISTINCT
    * 
FROM
    inventory 
WHERE
    ref LIKE '00%' 
    AND stock > 0 
GROUP BY
    ref 
ORDER BY
    date

Error:

错误：

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'inventory.scost' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.319000s

1055 - SELECT列表的表达式＃2不在GROUP BY子句中，并且包含非聚合列'inventory.scost'，它在功能上不依赖于GROUP BY子句中的列;这与sql_mode = only_full_group_by，时间：0.319000s不兼容

2 个解决方案

#1

Updated One way of doing it with strict mode turned on

更新了打开严格模式的一种方法

SELECT i.* 
  FROM inventory i JOIN (
    -- fetch the ref and the max date
    SELECT ref, MAX(date) date
      FROM inventory
     WHERE ref LIKE '00%'
       AND stock > 0
     GROUP BY ref
 ) q -- then join back to grab all other columns
    ON i.ref = q.ref
   AND i.date = q.date
 ORDER BY date, ref

or alternatively you can emulate ROW_NUMBER() OVER (PARTITION BY ref ORDER BY date) with session variables since MySQL doesn't yet support window functions

或者你可以用会话变量模拟ROW_NUMBER（）OVER（PARTITION BY ref ORDER BY date），因为MySQL还不支持窗口函数

SELECT ref, cost, stock, date, location
  FROM (
    SELECT *, @rn := IF(@g = ref, @rn + 1, 1) rn, @g := ref
      FROM inventory CROSS JOIN (
        SELECT @rn := 1, @g := NULL
      ) v
     WHERE ref LIKE '00%'
       AND stock > 0
     ORDER BY ref, date DESC
 ) q
 WHERE rn = 1
 ORDER BY date, ref

Output:

输出：

+---------+------+-------+---------------------+----------+
| ref     | cost | stock | date                | location |
+---------+------+-------+---------------------+----------+
| 00AE938 |  852 |     2 | 2017-06-14 08:24:43 | NY       |
| 00AG510 |  172 |     1 | 2017-06-14 08:24:43 | NY       |
| 00AG520 |  360 |     6 | 2017-06-14 08:24:43 | NY       |
| 00AG590 |  550 |     1 | 2017-06-15 06:31:03 | CA       |
| 00AE912 |  231 |    20 | 2017-06-16 06:01:16 | WA       |
| 00AD075 |  352 |     4 | 2017-06-19 06:01:14 | WA       |
+---------+------+-------+---------------------+----------+

Here is a dbfiddle demo for both queries

这是两个查询的dbfiddle演示

#2

SELECT DISTINCT
    * 
FROM
    inventory 
GROUP BY
    ref 
HAVING 
    ref LIKE '00%' 
    AND stock > 0 
ORDER BY
    date desc

Try this.

尝试这个。

#1