MySQL版本:8.x
给定这个模式+数据:
CREATE TABLE vehicle (
id int NOT NULL,
vin varchar(17) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE ubarequest (
vehicle_id INT NOT NULL,
year smallint(4) NOT NULL,
created date NOT NULL,
FOREIGN KEY (vehicle_id) REFERENCES vehicle(id)
);
INSERT INTO vehicle values (1, "AAA");
INSERT INTO vehicle values (2, "BBB");
INSERT INTO vehicle values (3, "AAA");
INSERT INTO vehicle values (4, "CCC");
INSERT INTO vehicle values (5, "AAA");
INSERT INTO ubarequest values (2, 2023, "1999-09-09"); # no; different vin
INSERT INTO ubarequest values (4, 2022, "1999-09-09"); # no; different vin
INSERT INTO ubarequest values (1, 2023, "2023-01-01"); # no; oldest in 23
INSERT INTO ubarequest values (3, 2023, "2023-02-01"); # yes
INSERT INTO ubarequest values (5, 2023, "2023-03-01"); # yes
INSERT INTO ubarequest values (1, 2022, "2023-04-01"); # no; oldest in 22
INSERT INTO ubarequest values (3, 2022, "2023-05-01"); # yes
INSERT INTO ubarequest values (3, 2022, "2023-06-01"); # yes
INSERT INTO ubarequest values (5, 2022, "2023-07-01"); # yes
INSERT INTO ubarequest values (1, 2022, "2023-08-01"); # yes
这是一个小提琴:http://sqlfiddle.com/#!9/2c5fe5f /2/0
对于给定的 vin“AAA”,我想选择所有 ubarequests,除了每年最旧的一个。因此,它应该接受所有 ubarequests 的车辆与 vin 'AAA'(不管来自哪个车辆行)每年,并选择所有比最旧的车辆更年轻的车辆。
在尝试了具有复杂子查询的类似问题的解决方案之后,我现在很困惑我想出的(同时给我预期的结果)是否真的是满足我需求的正确查询,或者它是否会在某些情况下失败:
select v.id, u.year, u.created
from ubarequest u
inner join vehicle v
on u.vehicle_id = v.id
where v.vin = "AAA" and u.created not in (
select min(created)
from ubarequest u
inner join vehicle v on u.vehicle_id = v.id
where v.vin = "AAA"
group by u.year)
order by u.created
在 MySQL 8.x 中,实现预期结果的一种方法是使用ROW_NUMBER。
在子查询中,我们每年分配最旧的值,其中 vin 等于 AAA,我们将在最终结果中过滤掉数字 1。
在这里查看演示