MySQL:从一列中选择包含值的多行 [英] MySQL: Select multiple rows containing values from one column

问题描述

我想在值列中找到具有"FORD"和"SILVER"且用户输入值为"200"的汽车的car_id:

I'd like to find the car_id's of the cars that have 'FORD' AND 'SILVER' AND the user input value of '200' in the value column:

table_cars

    +----+--------+----------+-----------+
    | id | car_id | name     | value     |
    +----+--------+----------+-----------+
    | 1  | 1      | MAKE     | FORD      |
    | 2  | 1      | CARLINE  | FIESTA    |
    | 3  | 1      | COLOR    | SILVER    |
    | 4  | 1      | TOPSPEED | 210KM/H   |
    | 5  | 2      | MAKE     | FORD      |
    | 6  | 2      | CARLINE  | FOCUS     |
    | 7  | 2      | COLOR    | SILVER    |
    | 8  | 2      | TOPSPEED | 200KM/H   |
    | 9  | 3      | MAKE     | HOLDEN    |
    | 10 | 3      | CARLINE  | ASTRA     |
    | 11 | 3      | COLOR    | WHITE     |
    | 12 | 3      | TOPSPEED | 212KM/H   |
    +----+--------+----------+-----------+

在这种情况下，应该只返回一个car_id:car_id = 2.

Which in this case should return only one car_id: car_id = 2.

如何为此创建SQL查询?

What would be the way to go to create the SQL query for this?

推荐答案

您拥有的是一个属性表.如果您想一次测试多个属性，则需要将表自身连接起来:

What you have is a properties table. When you want to test multiple properties at once you need to join the table to itself:

SELECT c0.car_id
FROM table_cars AS c0
JOIN table_cars AS c1 ON c1.car_id=c0.car_id
JOIN table_cars AS c2 ON c2.car_id=c1.car_id
WHERE c0.name='MAKE' AND c0.value='FORD'
AND c1.name='COLOR' AND c1.value='SILVER'
AND c2.name='TOPSPEED' AND c2.value='200KM/H'

在属性表中是否存在代理id是令人怀疑的.它似乎什么也没做.每个属性都不是其自己的实体.除非其他元素需要id，否则我将放弃它，并将car_id, name用作主键(复合主键).

Having the surrogate id present in a properties table is questionable. It doesn't seem to be doing anything; each property isn't an entity of its own. Unless the id is required by some other element, I'd get rid of it and make car_id, name the primary key (a composite primary key).

这篇关于MySQL:从一列中选择包含值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！