MySql：查找每个产品子类型中与类型最近的日期

Question

MySql：查找每个产品子类型中与类型最近的日期

3

如果这个问题之前已经有人提过，我很抱歉，但我在StackOverflow上没有找到完全相同的问题。

我有一个名为prices的表，如下所示，其中 type ， subtype 和 date 是主键：

type subtype      date       price
18  |  DFY  |  2019-06-27  |  230
18  |  DFY  |  2019-06-28  |  241
18  |  RGY  |  2019-06-28  |  234
23  |  NDO  |  2019-06-26  |  227
23  |  NDO  |  2019-06-27  |  241
23  |  SOG  |  2019-06-26  |  235
23  |  SOG  |  2019-06-27  |  239
23  |  SOG  |  2019-06-28  |  292
23  |  SOG  |  2019-06-29  |  238
23  |  SOG  |  2019-07-02  |  236

对于给定的类型，我需要检索每个其子类型中最多一个行，其中日期最接近给定日期，最多提前5天，但不得晚于给定日期。

例如，对于类型23和给定日期为2019-06-30，预期结果是这两行：

23  NDO 2019-06-27  241
23  SOG 2019-06-29  238

我尝试了这个：

select * from
    (select * from
        (select t.*, datediff('2019-06-30', t.date) as difference
            from prices t
            where t.type = 23
            and t.date < '2019-06-30'
            having difference <= 5
        ) temp
    order by temp.difference
    ) temp2
group by temp2.subtype

然而，返回的两行并不是差异最小的那两行。

由于我设计的数据访问代码的方式，必须全部在一条语句中完成。

- dtyfet

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Strawberry · Accepted Answer

模式（MySQL v5.7）

CREATE TABLE my_table (
  `type` INTEGER,
  `subtype` VARCHAR(3),
  `date` date,
  `price` INTEGER,
  PRIMARY KEY(type,subtype,date)
);

INSERT INTO my_table
  (`type`, `subtype`, `date`, `price`)
VALUES
  ('18', 'DFY', '2019-06-27', '230'),
  ('18', 'DFY', '2019-06-28', '241'),
  ('18', 'RGY', '2019-06-28', '234'),
  ('23', 'NDO', '2019-06-26', '227'),
  ('23', 'NDO', '2019-06-27', '241'),
  ('23', 'SOG', '2019-06-26', '235'),
  ('23', 'SOG', '2019-06-27', '239'),
  ('23', 'SOG', '2019-06-28', '292'),
  ('23', 'SOG', '2019-06-29', '238'),
  ('23', 'SOG', '2019-07-02', '236');

查询 #1

SELECT a.*
  FROM my_table a
  JOIN
      ( SELECT type
             , subtype
             , MAX(date) date
          FROM my_table
         WHERE date BETWEEN '2019-06-30' - INTERVAL 5 DAY AND '2019-06-30'
         GROUP
            BY type
             , subtype
      ) b
     ON b.type = a.type
    AND b.subtype = a.subtype
    AND b.date = a.date;

| type | subtype | date       | price |
| ---- | ------- | ---------- | ----- |
| 18   | DFY     | 2019-06-28 | 241   |
| 18   | RGY     | 2019-06-28 | 234   |
| 23   | NDO     | 2019-06-27 | 241   |
| 23   | SOG     | 2019-06-29 | 238   |

在DB Fiddle上查看