在mysql中映射同一个表中的多个值 [英] Mapping multiple values from the same table in mysql
问题描述
我必须使用另一个表中的值 id 来获取文本值表 1 包含值 id,表 2 包含名称和值 id
Hi I have to get text value using value id from another table Table 1 contains value ids and table 2 contains name and value ids
表 1 =
|SEVERITY | OCCURENCE | DETECTABILITY|
|2 | 3 | 4 |
表 2 =
id|name |value|
1 |Very High|5
2 |High |4
3 |Moderate |3
4 |Low |2
5 |Minor/Minimal|1
需要结果
SEVERITY | OCCURENCE | DETECTABILITY|
Low | Moderate | High |
推荐答案
我非常同意@Strawberry 的评论,你应该在你的 table1
中使用 table2.id
作为外键代码>.因为如果存在具有相同value
的重复记录,基于value
的join可能会导致多个映射.
I strongly agree with @Strawberry comment, you should have used table2.id
as foreign key in your table1
. Because join based on value
might lead to multiple mappings if there are duplicate records with same value
.
话虽如此,如果您在 table2
中有唯一的 value
,那么您可以执行以下操作.
That being said, if you've unique value
in table2
then you can do as follow.
SELECT
sev.`name` AS SEVERITY,
occ.`name` AS OCCURENCE,
det.`name` AS DETECTABILITY
FROM table1
LEFT JOIN table2 sev ON table1.SEVERITY = sev.`value`
LEFT JOIN table2 occ ON table1.OCCURENCE = occ.`value`
LEFT JOIN table2 det ON table1.DETECTABILITY = det.`value`;
注意:我们使用了 LEFT JOIN
来确保我们没有 table1
中提到的任何列的值我们应该仍然能够获取记录.
Note: We've used LEFT JOIN
to make sure if we don't have value of any of the column mentioned in table1
we should still be able to fetch records.
如果您确定这 3 列总是有价值,那么您也可以使用 INNER JOIN
.
If you are sure that these 3 columns will always have value than you can also use INNER JOIN
.
这篇关于在mysql中映射同一个表中的多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!