使用下表作为示例并将列出的查询作为基本查询，我想添加一种方法来仅选择具有最大 ID 的行!无需进行第二次查询!

TABLE VEHICLES

id      vehicleName
-----   --------
1       cool car
2       cool car
3       cool bus
4       cool bus
5       cool bus
6       car
7       truck
8       motorcycle
9       scooter
10      scooter
11      bus

TABLE VEHICLE NAMES

nameId  vehicleName
------  -------
1       cool car
2       cool bus
3       car
4       truck
5       motorcycle
6       scooter
7       bus

TABLE VEHICLE ATTRIBUTES

nameId  attribute
------  ---------
1       FAST
1       SMALL
1       SHINY
2       BIG
2       SLOW
3       EXPENSIVE
4       SHINY
5       FAST
5       SMALL
6       SHINY
6       SMALL
7       SMALL

和基本查询:

select a.*
  from vehicle         a
  join vehicle_names   b using(vehicleName)
  join vehicle_attribs c using(nameId)
 where c.attribute in('SMALL', 'SHINY')
 and a.vehicleName like '%coo%'
 group 
    by a.id
having count(distinct c.attribute) = 2;

所以我想要实现的是选择具有某些属性的行，这些行与名称匹配，但每个名称只有一个条目匹配 ID 最高的位置!

因此，此示例中的有效解决方案将返回以下行:

id      vehicleName
-----   --------
2       cool car
10      scooter

如果它在 id 上使用某种最大值

目前我得到了酷车和踏板车的所有条目。

我的真实世界数据库遵循类似的结构，其中有成千上万的条目，因此像上面这样的查询可以轻松返回 3000 多个结果。我将结果限制为 100 行以保持较低的执行时间，因为结果用于我网站上的搜索。我重复使用相同名称但 ID 不同的“车辆”的原因是不断添加新模型，但我保留旧模型供那些想要挖掘它们的人使用!但是在按汽车名称搜索时，我不想只返回最新的一张 ID 最高的旧卡!

正确的答案将调整我在上面提供的我当前正在使用的查询，并让它只返回名称匹配但具有最高 ID 的行!

如果这不可能，请提供有关如何在不大量增加搜索执行时间的情况下实现我想要的结果的建议!

最佳答案

如果你想保持你的逻辑，我会这样做:

select a.*
from vehicle a
    left join vehicle a2 on (a.vehicleName = a2.vehicleName and a.id < a2.id)
    join vehicle_names   b on (a.vehicleName = b.vehicleName)
    join vehicle_attribs c using(nameId)
where c.attribute in('SMALL', 'SHINY')
    and a.vehicleName like '%coo%'
    and a2.id is null
group by a.id
having count(distinct c.attribute) = 2;

产量:

+----+-------------+
| id | vehicleName |
+----+-------------+
|  2 | cool car    |
| 10 | scooter     |
+----+-------------+
2 rows in set (0.00 sec)

As other said, normalization could be done on few levels:

Keeping your current vehicle_names table as the primary lookup table, I would change:

update vehicle a
    inner join vehicle_names b using (vehicleName)
set a.vehicleName = b.nameId;
alter table vehicle change column vehicleName nameId int;

create table attribs (
    attribId int auto_increment primary key,
    attribute varchar(20),
    unique key attribute (attribute)
);
insert into attribs (attribute)
    select distinct attribute from vehicle_attribs;
update vehicle_attribs a
    inner join attribs b using (attribute)
set a.attribute=b.attribId;
alter table vehicle_attribs change column attribute attribId int;

这导致了以下查询:

select a.id, b.vehicleName
from vehicle a
    left join vehicle a2 on (a.nameId = a2.nameId and a.id < a2.id)
    join vehicle_names b on (a.nameId = b.nameId)
    join vehicle_attribs c on (a.nameId=c.nameId)
    inner join attribs d using (attribId)
where d.attribute in ('SMALL', 'SHINY')
    and b.vehicleName like '%coo%'
    and a2.id is null
group by a.id
having count(distinct d.attribute) = 2;

关于MySQL选择具有最大ID并匹配其他条件的行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4381986/