在2个表中使用where子句选择mysql数据 [英] Select mysql data with where clause in 2 tables

问题描述

我有2个mysql表，如下例所示:

I have 2 mysql tables like in the example below:

汽车

Id     CAR NAME          AGE

1   Ford        2 years
2   AUDI        1 years 
3   Ford        2 years

选项

Id  id_car   option

1    1      ESP
2    2          ABS
3    3          ABS
4    3          ESP

我需要选择所有2岁且具有ABS和ESP的汽车. 因此它应在此示例中返回:3福特

And I need to select all cars of 2 years old wich have ABS AND ESP. So it should return in this example: 3 Ford

如果您有任何想法...

If you have any idea...

谢谢

推荐答案

分组依据/将确保汽车具有两个所需的功能.

The group by/having will ensure that the car has both of the desired features.

select c.id, c.name
    from cars c
        inner join options o
            on c.id = o.id_car
                and o.option in ('ABS','ESP')
    where c.age = 2
    group by c.id, c.name
    having count(distinct o.option) = 2

这篇关于在2个表中使用where子句选择mysql数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！