SQL查询根据其他表列值从表中获取项目列表？

I have these 2 tables: BluePrint:

我有这两个表:BluePrint:

ID| BlueprintID| CarID

Car:

ID, Name, Color

I need to get all the cars based on blueprintID. I am using SQLLite.

我需要根据blueprintID获得所有汽车。我正在使用SQLLite。

Thanks

3 个解决方案

#1

This can be done with either a subquery:

这可以使用子查询来完成:

SELECT *
FROM Car
WHERE ID IN (SELECT CarID
             FROM BluePrint
             WHERE BlueprintID = ?)

or a join:

或加入:

SELECT Car.*
FROM Car
JOIN BluePrint ON Car.ID = BluePrint.CarID
WHERE BlueprintID = ?

(If it is possible to have multiple blueprints for one car, the second query needs SELECT DISTINCT.)

(如果一辆车可能有多个蓝图,则第二个查询需要SELECT DISTINCT。)

#2

SELECT c.*,B.BlueprintID FROM CAR C,BluePrint B WHERE C.ID=B.CarID

SELECT c。*,B.BlueprintID FROM CAR C,BluePrint B WHERE C.ID = B.CarID

#3

You need to join one table to the other.

您需要将一个表连接到另一个表。

SELECT c.ID, c.Name, c.Color FROM CAR c JOIN BLUEPRINT b ON b.CarID = c.ID WHERE b.BlueprintID = [YOURBLUEPRINTID]

SELECT c.ID,c.Name,c.Color FROM CAR c JOIN BLUEPRINT b ON b.CarID = c.ID WHERE b.BlueprintID = [YOURBLUEPRINTID]

I have given both tables an alias, it makes it easier especially when you have lots of fields. If a field with the same name appears in both tables and you don't specify which table by using something like c.Name or Car.Name` you will get an ambiguous field name error.

我给了两个表别名,它使得它更容易,特别是当你有很多字段时。如果两个表中都出现一个具有相同名称的字段,并且您没有使用c.Name或Car.Name`这样的内容指定哪个表,则会出现不明确的字段名称错误。

#1