给定以下架构:
CREATE TABLE IF NOT EXISTS companies (
id serial,
name text NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS cars (
id serial,
make text NOT NULL,
year integer NOT NULL,
company_id INTEGER REFERENCES companies(id),
PRIMARY KEY (id)
);
INSERT INTO companies (id, name) VALUES
(1, 'toyota'),
(2, 'chevy');
INSERT INTO cars (make, year, company_id) VALUES
('silverado', 1995, 2),
('malibu', 1999, 2),
('tacoma', 2017, 1),
('custom truck', 2010, null),
('van custom', 2005, null);
如何选择汽车行,仅显示给定公司的最新汽车?
例如
select make, companies.name as model, year from cars
left join companies
on companies.id = cars.company_id
order by make;
输出
make | model | year
--------------+--------+------
custom truck | | 2010
malibu | chevy | 1999
silverado | chevy | 1995
tacoma | toyota | 2017
van custom | | 2005
但是我只想显示最新的“雪佛兰”
make | model | year
--------------+--------+------
custom truck | | 2010
malibu | chevy | 1999
tacoma | toyota | 2017
van custom | | 2005
并且仍然可以按“制造商”进行排序,并显示没有空company_id的汽车。
小提琴链接:https : //www.db-fiddle.com/f/5Vh1sFXvEvnbnUJsCYhCHf/0
可以基于Set Math(离散数学)完成SQL。因此,您希望所有汽车的数量减去年数小于给定公司ID的最大年份的汽车数量。
所有汽车的集合:
select * from cars
年份小于给定公司ID的最大年份的所有汽车的集合:
select a.id from cars a, cars b where a.company_id = b.company_id and a.year < b.year
一套减去另一套:
select * from cars where id not in (select a.id from cars a, cars b where a.company_id = b.company_id and a.year < b.year)
包含空的company_id的结果,因为它们被从id比较中排除:
make | model | year
--------------+--------+------
custom truck | | 2010
malibu | chevy | 1999
tacoma | toyota | 2017
van custom | | 2005
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句