MySQL - 根据表中行的值从不同表中检索行值

Question

我觉得这里应该使用 CASE，但我不确定。我正在尝试从通用表中获取性别 (m/f)，并根据性别从相应的表中获取构建。例如，当性别是 m 时，查询应该通过转到 males 表并选择 male_build 来知道这一点。我将输出与示例查询放在一起，以了解我要完成的工作:

members table
member_id | member_name
------------------------
     1         Herb
     2         Karen
     3         Megan
     4         Pablo

males table
male_id | member_id | male_build
---------------------------------
    1         1        muscular
    2         4          fat

females table
female_id | member_id | female_build
-------------------------------------
    1           2           thin
    2           3           fat 

general table
general_id | member_id | sex
-----------------------------
     1          1         m
     2          2         f
     3          3         f
     4          4         m

输出应该是这样的:

1. Herb is a muscular male.
2. Karen is a thin female.
3. Megan is a fat female.
4. Pablo is a fat male.
   -> query = "SELECT g.general_id, g.member_id, g.sex,   
              (CASE when g.sex=m THEN SELECT ma.male_build AS build 
                   FROM males ma WHERE ma.member_id=g.member_id,
              CASE when g.sex=f THEN SELECT fe.female_build AS build 
                   FROM females fe WHERE fe.member_id=g.member_id),
               m.member_name 
               FROM members m
               JOIN members m ON g.member_id=m.member_id 
               WHERE g.sex='m' OR g.sex='f'";

正在为此任务寻找有效的查询。

Answer 1

当您不得不执行此类查询时，最好重新考虑您的数据库设计架构。话虽如此，这应该有效:

SELECT mem.*, g.*, coalesce(m.male_build, f.female_build) as build
from members_table mem
inner join general g on mem.meber_id = g.member_id
left join males m on mem.member_id = m.member_id
left join females f on mem.member_id = f.member_id