MySQL - 根据表中行的值从不同的表中检索行值 [英] MySQL - Retrieve row value from different table depending on value of row in a table

问题描述

我有一种感觉CASE应该在这里使用，但我不确定。我试图从一般表中取出性别（m / f），并根据性别，从相应的表中获取构建。例如，当性别是m时，查询应该知道通过转到男性表并选择male_build。我将输出与示例查询放在一起，以便了解我要完成的任务：

I have a feeling CASE should be used here but I am not sure. I'm trying to take the sex (m/f) from the general table and depending on the sex, get the build from the corresponding table. For example, when the sex is m, the query should know that by going to the males table and selecting the male_build. I put together an output along with a sample query to give an idea of what I'm trying to accomplish:

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'";

寻找有效查询此任务。

推荐答案

当你不得不做这样的查询时，重新考虑你的数据库设计模式是个好主意。话虽如此，这应该有效：

When you are having to do queries like this, it is a good idea to rethink your database design schemas. That being said, this should work:

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

这篇关于MySQL - 根据表中行的值从不同的表中检索行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！