SQL关联查询结果格式异常：如何将(id,值)转为对应纯值？

原本参考用另一表对应值替换本表ID的方案有效，但后续出现异常。表结构如下：

name表 #

+----------+-----------+-----------+
| name_id  | color_id  | shape_id  |
+----------+-----------+-----------+
|    1     |    11     |    01     |
+----------+-----------+-----------+
|    2     |    22     |    02     |
+----------+-----------+-----------+
|    3     |    33     |    03     |
+----------+-----------+-----------+

color表 #

+----------+-------+
| color_id | color |
+----------+-------+
|    11    |   R   |
+----------+-------+
|    22    |   G   |
+----------+-------+
|    33    |   B   |
+----------+-------+

shape表 #

+----------+-------+
| shape_id | shape |
+----------+-------+
|    01    |   S   |
+----------+-------+
|    02    |   T   |
+----------+-------+
|    03    |   C   |
+----------+-------+

期望输出：

+----------+-----------+
|   color  |   shape   |
+----------+-----------+
|    R     |     S     |
+----------+-----------+
|    G     |     T     |
+----------+-----------+
|    B     |     C     |
+----------+-----------+

实际输出：

+----------+-----------+
|   color  |   shape   |
+----------+-----------+
| (11,"R") |  (01,"S") |
+----------+-----------+
| (22,"G") |  (02,"T") |
+----------+-----------+
| (33,"B") |  (03,"C") |
+----------+-----------+

使用的查询语句：

SELECT 
   color, shape
FROM 
   name
      INNER JOIN 
         color ON name."color_id" = color."color_id"
      INNER JOIN 
         shape ON name."shape_id" = shape."shape_id"

询问是否可以通过修改SQL语法直接将(11,"R")这类格式转为R。

问题出在你直接查询了表名color和shape，这会返回整个行的记录（以元组形式展示），而不是对应表中的color和shape字段。你需要明确指定要查询的字段，修改后的SQL语句如下：

SELECT 
   color.color, shape.shape
FROM 
   name
      INNER JOIN 
         color ON name."color_id" = color."color_id"
      INNER JOIN 
         shape ON name."shape_id" = shape."shape_id"

或者可以给表设置别名，让语句更简洁：

SELECT 
   c.color, s.shape
FROM 
   name n
      INNER JOIN 
         color c ON n."color_id" = c."color_id"
      INNER JOIN 
         shape s ON n."shape_id" = s."shape_id"

这样就能精准获取到color表中的color字段和shape表中的shape字段，得到你期望的输出结果。

内容的提问来源于stack exchange，提问作者deeded307