在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屋！