从联接表中获取多个不同的值 [英] Getting multiple different values from a joined table

问题描述

如果没有多个子查询，我想不出一个很好的查询方法。我无法重组表格，因此这不是一种选择。设置如下：

I can't figure out a good way of querying this without multiple sub-queries. I can't restructure the tables, so that's not an option. Here's the setup:

person:
person_id
person_want_id,
person_need_id,
person_ability_id,
person_willingness_id

person_status_types:
status_type_id
status_type_name

面对面的每个ID之一都是person_status_types类型。我需要提取的是每个id的本人的status_type_name。

Each one of the id's that are in person is of type person_status_types. What I need to pull out is the status_type_name for each of the id's in person.

所以返回语句如下：

person_id | person_want_name | person_need_name | person_ability_name | person_willingness_name

现在我只在做4个子查询，但是必须有一种更清洁的方法它。另外，如果您想提一个更好的数据库结构，我也很乐意为将来的数据库生产做准备。

Right now I'm just doing 4 subqueries, but there has to be a cleaner way of doing it. Also, if you want to mention a better database structure, I'm open minded to it for future database production.

推荐答案

SELECT p.person_id
      ,want.status_type_name AS person_want_name
      ,need.status_type_name AS person_need_name
      ,abt.status_type_name  AS person_ability_name
      ,wil.status_type_name  AS person_willingness_name
FROM person p
LEFT JOIN person_status_types want ON p.person_want_id = want.status_type_id
LEFT JOIN person_status_types need ON p.person_need_id = need.status_type_id
LEFT JOIN person_status_types abt  ON p.person_ability_id = abt.status_type_id
LEFT JOIN person_status_types wil ON p.person_willingness_id = wil.status_type_id

使用 LEFT JOIN 避免丢失行。

而且您不能单引号标识符（如另一个答案所示）。

Use LEFT JOIN to avoid loosing rows.
And you can't single-quote identifiers (like another answer suggests). Single quotes are for values.

由于这引起了一些争论，因此请澄清一下

Since this has sparked some debate, to clarify once and for all:

• 双引号 根据任何ANSI标准定界标识符。所有重要的RDBMS都普遍支持 ，MySQL是唯一的例外。您可以使用<$ c $在MySQL中修复此问题c> SET sql_mode ='ANSI';

• double quotes " delimit identifiers according to any ANSI standard. This is universally supported by all RDBMS that matter, MySQL being the only exception. You can fix this in MySQL with SET sql_mode = 'ANSI';

某些RDBMS接受其他替代分隔符，如 [] 用于SQL Server。

Some RDBMS accept additional alternative delimiters like [] for SQL server.

单引号 '分隔<符合ANSI标准的em> values 。一些RDBMS（例如MySQL或SQL Server）允许它们在列名周围作为通用字符串定界符，但都不能在表名周围接受。

single quotes ' delimit values according to the ANSI standard. Some RDBMS (like MySQL or SQL Server) tolerate them them around column names as general string delimiters. Neither accepts them around table names though.

反引号 ´作为标识符的分隔符仅是MySQL的怪癖，同样，您可以在MySQL wi th SET sql_mode ='ANSI';

backticks ´ as delimiter for identifiers are a MySQL oddity only. Again, you can fix this in MySQL with SET sql_mode = 'ANSI';

一个列表以及它们各自的标识符分隔符。

这篇关于从联接表中获取多个不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！