mysql - 选择具有最大值的唯一行(有条件)

Question

通过下面的表格和数据，我试图获得最高的 effective_from每个唯一 brand 小于当前时间戳的值/model组合 - 实际上是每件商品的当前价格。

CREATE TABLE things
(`id` int, `brand` varchar(1), `model` varchar(5), `effective_from` int, `price` int);

INSERT INTO things
(`id`, `brand`, `model`, `effective_from`, `price`)
VALUES
(1, 'a', 'red', 1402351200, 100),
(2, 'b', 'red', 1402351200, 110),
(3, 'a', 'green', 1402391200, 120),
(4, 'b', 'blue', 1402951200, 115),
(5, 'a', 'red', 1409351200, 150),
(6, 'a', 'blue', 1902351200, 140),
(7, 'b', 'green', 1402358200, 135),
(8, 'b', 'blue', 1902358200, 155),
(9, 'b', 'red', 1902751200, 200),
(10, 'a', 'red', 1908351200, 210),
(11, 'a', 'red', 1402264800, 660);

到目前为止，当我为特定 brand 添加条件时，我已经成功获得了我要查找的行。/model组合，但不知道如何获取所有唯一行组合的当前价格。

SELECT *
FROM things
WHERE effective_from<UNIX_TIMESTAMP()
AND brand='a'
AND model='red'
ORDER BY effective_from DESC
LIMIT 1;

如果当前时间戳是 1402404432结果应如下:

(1, 'a', 'red', 1402351200, 100),
(3, 'a', 'green', 1402391200, 120),
(2, 'b', 'red', 1402351200, 110),
(7, 'b', 'green', 1402358200, 135),

Answer 1

我猜你正在追求这个。否则请告知...

SELECT x.*
  FROM things x
  JOIN 
     ( SELECT brand
            , model
            , MAX(effective_from) max_effective_from 
         FROM things 
        WHERE effective_from <= UNIX_TIMESTAMP() 
        GROUP 
           BY brand
            , model
     ) y 
    ON y.brand = x.brand 
   AND y.model = x.model 
   AND y.max_effective_from = x.effective_from;
+------+-------+-------+----------------+-------+
| id   | brand | model | effective_from | price |
+------+-------+-------+----------------+-------+
|    1 | a     | red   |     1402351200 |   100 |
|    2 | b     | red   |     1402351200 |   110 |
|    3 | a     | green |     1402391200 |   120 |
|    7 | b     | green |     1402358200 |   135 |
+------+-------+-------+----------------+-------+

SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1402404432 |
+------------------+