mysql - 连接2个表，需要根据最高价格显示所有字段，并且不同字段没有重复项

Question

我需要显示每个制造商最昂贵的汽车的所有字段。

使用的两个表是:

CREATE TABLE CARS
(
Vehicle_Identification_Number int(10) NOT NULL UNIQUE,
Manufacturers_ID int(5),
Owner_ID int(10),
Model varchar(25),
Manufaturer_Year int(4),
Mileage int(10),
Price int(10),
PRIMARY KEY (Vehicle_Identification_Number),
FOREIGN KEY (Manufacturers_ID) REFERENCES MANUFACTURERS (Manufacturers_ID),
FOREIGN KEY (Owner_ID) REFERENCES OWNERS (Owner_ID)
)
ENGINE= innodb;

CREATE TABLE MANUFACTURERS
(
Manufacturers_ID int(5) UNIQUE,
Name varchar(15) UNIQUE,
City varchar(30),
State char(2),
Zip char(5),
Phone char(10),
PRIMARY KEY (Manufacturers_ID)
)
ENGINE= innodb;

到目前为止我所做的工作是:

SELECT *
FROM MANUFACTURERS
 LEFT JOIN CARS
  ON MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
UNION
SELECT *
FROM MANUFACTURERS
 RIGHT JOIN CARS
  ON MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
ORDER BY Price DESC;

这就是我陷入困境的地方，我尝试过的所有操作都给我留下了错误消息。任何帮助将不胜感激。

Answer 1

我不确定你所说的“不同字段中没有重复项”是什么意思。

各个厂家的最高价格就是这套。 (最高价格的汽车可能不止一辆。)

select Manufacturers_ID, max(Price)
from cars
group by Manufacturers_ID;

加入该集合中的两列。 (第二个内部联接，如下。)

select MANUFACTURERS.*, CARS.*
from CARS
inner join MANUFACTURERS 
        on MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
inner join (select Manufacturers_ID, max(Price) as Price
            from cars
            group by Manufacturers_ID) as MOST_EXPENSIVE
        on CARS.Manufacturers_ID = MOST_EXPENSIVE.Manufacturers_ID
       and CARS.Price = MOST_EXPENSIVE.Price;