时间戳

首页 > 解决方案 > 如何查询没有直接关系的不同表并组合不明确的列

mysql - 如何查询没有直接关系的不同表并组合不明确的列

问题描述

在原始问题回复者的反馈后，此问题已被编辑

我有一个复杂的 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并抢fieldID之48。然后，我们必须查看HS_CUSTOMFIELDVALUES表格以找到 的fieldID和48Bob 的学生 ID 101。然后我们可以看到 Bob 在 Football 中，并且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

提前致谢！

标签： mysqlsql

解决方案

使用子查询从每对表中获取活动。然后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

演示

推荐阅读