如何查询没有直接关系的异种表并组合不明确的列

Question

此问题是根据原始问题回答者的反馈编辑的

我有一个复杂的 MySQL 问题，我需要跨不一定有任何要连接的不同表进行查询。我正在处理第三方系统，因此无法对数据库的结构进行任何更改。

为了简洁起见，我把这个问题过于简单化了，但我能想到的最简单的解释方法是：我需要得到一份学生和他们的主要活动的名单。学生包含在一个表中，但问题是他们所在的活动是通过其他表引用的，这些表只有粗略的关系。每个建筑物都有 (2) 个自定义字段信息表。"XX_CUSTOMFIELDNAMES"具有自定义域的名称，"XX_CUSTOMFIELDVALUES"包含相关值。在本例中，自定义字段名称为"活动">

表格和示例数据：

======================================
| STUDENTS                           |
======================================
| studentID | studentName | building | 
--------------------------------------
| 101       | Bob         | HS       |
| 102       | Amy         | MS       |
| 103       | Jim         | ES       |
| 104       | Andy        | HS       |
--------------------------------------
=======================         ====================================
| HS_CUSTOMFIELDNAMES |         |      HS_CUSTOMFIELDVALUES        |
=======================         ====================================
| fieldID | fieldname |         | fieldID | studentID | fieldvalue |
-----------------------         ------------------------------------
| 48      | Activity  |         | 48      | 101       | Football   |
| 49      | Health    |         | 49      | 101       | Asthma     |
-----------------------         ------------------------------------
=======================         ====================================
| MS_CUSTOMFIELDNAMES |         |      MS_CUSTOMFIELDVALUES        |
=======================         ====================================
| fieldID | fieldname |         | fieldID | studentID | fieldvalue |
-----------------------         ------------------------------------
| 44      | Activity  |         | 44      | 102       | Track      | 
-----------------------         ------------------------------------
=======================         ====================================
| ES_CUSTOMFIELDNAMES |         |      ES_CUSTOMFIELDVALUES        |
=======================         ====================================
| fieldID | fieldname |         | fieldID | studentID | fieldvalue |
-----------------------         ------------------------------------
| 43      | Activity  |         | 43      | 103       | Band       |
| 42      | Teacher   |         | 42      | 103       | Mr. Smith  |
-----------------------         ------------------------------------

举个例子：

要确定Bob从事哪些活动，我们必须查看他的建筑HS和学生证101。由于他的建筑HS，我们得看HS-CUSTOMFIELDNAMES表才能找到字段名称Activity，并抓取48的fieldID。然后，我们必须查看HS_CUSTOMFIELDVALUES表以找到48的fieldID和Bob的学生ID为101。然后我们可以看到鲍勃在足球中，并且health值为asthma.但是，我们只想查询Activity或缺少它。

我有以下查询，它没有获得 NULL 活动值，也没有像您所看到的那样组合"活动"字段(因为否则我会收到"不明确的列名"错误)。此外，我跳过了"ES"构建，以使此处的查询更短。

SELECT
s.studentID, s.studentName, s.building, COALESCE(HS.fieldvalue, MS.fieldvalue, ES.fieldvalue) AS 'Activity'
FROM
students s
LEFT JOIN
HS_CUSTOMFIELDVALUES HS
ON
s.studentID = HS.studentID 
LEFT JOIN
HS_CUSTOMFIELDNAMES AS HSF
ON HSF.fieldID = HS.fieldID AND HSF.fieldname = 'Activity'
LEFT JOIN
MS_CUSTOMFIELDVALUES MS
ON
s.studentID = MS.studentID 
LEFT JOIN
MS_CUSTOMFIELDNAMES AS MSF
ON MSF.fieldID = MS.fieldID AND MSF.fieldname = 'Activity'
LEFT JOIN
ES_CUSTOMFIELDVALUES ES
ON
s.studentID = ES.studentID 
LEFT JOIN
ES_CUSTOMFIELDNAMES AS ESF
ON ESF.fieldID = ES.fieldID AND ESF.fieldname = 'Activity'

实际查询结果：

==================================================
| studentID | studentName | building | Activity  |
--------------------------------------------------
| 101       | Bob         | HS       | Asthma    |    
| 101       | Bob         | HS       | Football  |
| 102       | Amy         | MS       | Track     |
| 103       | Jim         | ES       | Band      |
| 103       | Jim         | ES       | Mr. Smith |
| 104       | Andy        | HS       |           |
--------------------------------------------------

由于第一条和第五条记录都不是Activity，我不需要返回这些结果，以便输出如下所示：

所需的查询结果：

=================================================
| studentID | studentName | building | activity |
-------------------------------------------------
| 101       | Bob         | HS       | Football |
| 102       | Amy         | MS       | Track    |
| 103       | Jim         | ES       | Band     |
| 104       | Andy        | HS       |          |
-------------------------------------------------

SQL 小提琴：http://sqlfiddle.com/#!9/b747ce/1/0

提前感谢！

使用子查询仅从每对表中获取活动。然后，LEFT JOIN每个查询与students表，并使用COALESCE()指定优先级。

SELECT
s.studentID, s.studentName, s.building, COALESCE(HS.fieldvalue, MS.fieldvalue, ES.fieldvalue) AS 'Activity'
FROM
students s
LEFT JOIN
(SELECT HS.studentID, HS.fieldvalue
FROM 
HS_CUSTOMFIELDVALUES HS
JOIN
HS_CUSTOMFIELDNAMES AS HSF
ON HSF.fieldID = HS.fieldID
WHERE HSF.fieldname = 'Activity') AS HS
ON S.studentID = HS.studentID
LEFT JOIN
(SELECT MS.studentID, MS.fieldvalue
FROM 
MS_CUSTOMFIELDVALUES MS
JOIN
MS_CUSTOMFIELDNAMES AS MSF
ON MSF.fieldID = MS.fieldID
WHERE MSF.fieldname = 'Activity') AS MS
ON S.studentID = MS.studentID
LEFT JOIN
(SELECT ES.studentID, ES.fieldvalue
FROM 
ES_CUSTOMFIELDVALUES ES
JOIN
ES_CUSTOMFIELDNAMES AS ESF
ON ESF.fieldID = ES.fieldID
WHERE ESF.fieldname = 'Activity') AS ES
ON S.studentID = ES.studentID

演示