MySQL：从一列中选择包含值的多行

发布于 2024-09-25 04:31:22 字数 853 浏览 0 评论 0原文

的 car_id，并且在值列中用户输入值为“200”：

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。

我想找到具有“FORD”和“SILVER”的汽车为此创建 SQL 查询的方法是什么？

原文

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   |
    +----+--------+----------+-----------+

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泡沫很甜 2024-10-02 04:31:23

您拥有的是一个属性表。当您想要一次测试多个属性时，您需要将表连接到自身：

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 主键（复合主键）。

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'

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).

回复收藏 0 原文

负佳期 2024-10-02 04:31:23

我假设每辆车都需要有可变的参数，否则你不会采用这样的设置。如果 MAKE、CARLINE、COLOR 和 TOPSPEED 各有自己的列，那就容易多了。

但是，使用您提供的表，您需要使用子查询。 http://dev.mysql.com/doc/refman/5.0/en /subqueries.html

查询应如下所示（未经测试）：

SELECT * FROM table_cars WHERE id IN (SELECT * FROM table_cars WHERE name="MAKE" AND value="FORD") AND id IN (SELECT * FROM table_cars WHERE name="COLOR" AND value="SILVER") AND id IN (SELECT * FROM table_cars WHERE name="TOPSPEED" AND value="200KM/H")

I assume that every car needs to have variable parameters, otherwise you wouldn't have gone with a setup like this. It would be much easier if MAKE, CARLINE, COLOR, and TOPSPEED each had their own column.

Using the table you've provided, however, you need to use subqueries. http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

The query should look something like this (untested):

SELECT * FROM table_cars WHERE id IN (SELECT * FROM table_cars WHERE name="MAKE" AND value="FORD") AND id IN (SELECT * FROM table_cars WHERE name="COLOR" AND value="SILVER") AND id IN (SELECT * FROM table_cars WHERE name="TOPSPEED" AND value="200KM/H")

回复收藏 0 原文

~没有更多了~