SQL 具有多个连接的 SQLite 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8160671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite query with multiple joins
提问by Shaun
This is pretty much just a simple join statemnet I believe. I've not worked with SQL much lately and seem to have forgotten how to do this. What I have is an item with few columns in it that reference another table for the name of that field. Like this:
我相信这几乎只是一个简单的连接语句。我最近很少使用 SQL，似乎忘记了如何执行此操作。我所拥有的是一个项目，其中几列引用了另一个表作为该字段的名称。像这样：
id, name, effect1, effect2, effect3, effect4
The effects reference another table that only has a, id, and namecolumns. What I'm trying to do is run a query that will pull those names for each of those effects.
效果可以引用另一个表只拥有a，id和name列。我正在尝试做的是运行一个查询，该查询将为每个效果提取这些名称。
Something like:
就像是：
SELECT i.name,e.name AS effect1, e.name AS effect2, e.name AS effect3,
e.name AS effect4
FROM item i, effects e
WHERE i.effect1 = e.name
AND i.effect2 = e.name
AND i.effect3 = e.name
AND i.effect4 = e.name
So, say I have an item that has values like this:
因此，假设我有一个具有以下值的项目：
Toast, 1, 2, 3, 4
and the effects are:
效果如下：
1, burned
2, untoasted
3, wet
4, texas
I want it to display toast, burned, untoasted, wet, texas
我想让它显示 toast, burned, untoasted, wet, texas
And ideas?
和想法？
update:
更新：
Table items
id, name, weight, value, effect1,effect2,effect3,effect4
Table effects
id, name
In the effect1,... columns are the id number for the corresponding item in the effect table. A lot ofitems are going to share the same effects, so instead of inflating this already large database with redundant data, I decided to use a join to save space. At the same time I managed how to forget to do it, lol
在 effect1,... 列中是 effect 表中相应项目的 id 号。许多项目将共享相同的效果，因此我决定使用连接来节省空间，而不是用冗余数据来膨胀这个已经很大的数据库。同时我设法忘记这样做，哈哈
Update #2 This is the effect I'm going for, but on more than one of the effect columns
更新 #2 这是我想要的效果，但在不止一个效果列上
SELECT i.name, i.weight,i.value, e.name AS 'effect 1'
FROM ingredients i JOIN effects e ON effects._id=i.effect1
This works for 1, but if I try to do multiple it just crashes. Any ideas how I can get that effect for all 4 effects?
这适用于 1，但如果我尝试做多个它只会崩溃。有什么想法可以为所有 4 种效果获得这种效果吗？
回答by Erwin Brandstetter
You need a distinct join for every column:
每列都需要一个不同的连接：
SELECT i.name
,i.weight
,i.value
,e1.name AS effect1
,e2.name AS effect2
,e3.name AS effect3
,e4.name AS effect4
FROM ingredients i
LEFT JOIN effects e1 ON e1.id = i.effect1
LEFT JOIN effects e2 ON e2.id = i.effect2
LEFT JOIN effects e3 ON e3.id = i.effect3
LEFT JOIN effects e4 ON e4.id = i.effect4
Make that LEFT JOINs so you don't lose the row if any of the effects is missing in the effects table.
Also, this query depends upon effects.idbeing unique.
进行 LEFT JOIN，这样如果效果表中缺少任何效果，您就不会丢失该行。
此外，此查询取决于effects.id是unique。
Here is another way to do the same:
这是执行相同操作的另一种方法：
SELECT i.name
,i.weight
,i.value
,(SELECT e.name FROM effects e WHERE e.id = i.effect1) AS effect1
,(SELECT e.name FROM effects e WHERE e.id = i.effect2) AS effect2
,(SELECT e.name FROM effects e WHERE e.id = i.effect3) AS effect3
,(SELECT e.name FROM effects e WHERE e.id = i.effect4) AS effect4
FROM ingredients i
Concerning the db layout:
关于数据库布局：
If every ingredienthas 4 effectsyour db design is fine. If the number of effects vary or you have additional information per effect, you might consider an n:mrelationship between ingredientsand effects, implemented by an additional table. (Replacing the four effect* columns.)
Could look like this:
如果每个ingredient都有 4effects你的数据库设计就可以了。如果效应的数量不同或者您对每个效应有额外的信息，您可以考虑和n:m之间的关系，由附加表实现。（替换四个 effect* 列。）可能如下所示：ingredientseffects
CREATE TABLE ingredients_effects
(
ingredients_id integer references ingredients(id)
,effects_id integer references effects(id)
-- more attributes like quantity or notes?
);
The fine manual has the details.
该精细的手工有细节。
