理解复杂的 MySQL JOIN

Vijay-Srivastava

4.50/5 (8投票s)

2013 年 11 月 20 日

CPOL

13分钟阅读

67013

741

使用嵌套 MySQL 查询、JOIN 操作、LIKE 运算符和聚合函数（例如 GROUP BY、GROUP_CONCAT 等）从多个表中提取复杂数据。

下载 sql_join.zip - 2.5 KB

引言

本文的目的是展示 MySQL JOIN 操作、嵌套 MySQL 查询（中间或临时结果集表）和聚合函数（如 GROUP BY）的强大功能。有关 MySQL join 操作的基础知识，可以参考链接 1 或链接 2。

为了解释各种 MySQL JOIN 操作，我选择了一个构想管理系统的领域。我先简要描述一下这个领域。

系统用户：在此构想管理系统中，有一组属于不同部门的注册用户。此系统的基本要求是跟踪所有注册用户提交的构想。
构想：一个构想可以由一个或多个用户发起，因此一个构想可以有一个或多个贡献者。然而，只有一个贡献者可以提交构想，其他贡献者可以列为构想的贡献者。
用户角色：用户可以拥有多个角色，例如普通用户或管理员用户。管理员用户可以将已提交构想的状态从待定状态（0）更改为已接受（1）或已拒绝（2）。
构想状态更新：状态更改后，所有相关的贡献者将获得一些积分作为奖励。
构想事件生成：根据构想的进展，可以生成一些事件，然后由管理员用户将其作为构想事件馈送到系统中。
生成汇总报告：这个构想管理系统的主要目标是处理用户和构想信息，以显示汇总信息，例如近期构想列表、基于积分的顶尖用户或顶尖团队列表，以激励用户提交更多构想。

在本文中，我将解释如何使用 MySQL JOIN 和聚合运算符在一个复杂的 MySQL 查询中获取这些汇总信息。我建议，在查看实际的 MySQL 查询之前，先自己尝试解决本文中提到的问题，以获得更好的理解。

本系统包含以下表，我们将在本文中一直引用这些表来解决问题。有关示例数据，请下载数据库转储并将其导入您的 MySQL 环境（请参阅代码部分的使用）。

首先，我将简要描述这些表。

user_info

id full_name dep_id points

jack JACK D. 2 2

jackson M.S. Jackson 3 3

alice Alice W 2 1

bob Bob S. 2 2

此表列出了所有注册用户的信息。
属性
- id：- 用户的自动生成 ID
- full_name：- 用户的全名
- dep_id：- 用户的部门 ID，这是一个外键，指向 dept_info 表中的 'id' 字段
- points：- 用户通过构想提交收到的总积分

user_idea

idea_id	user_id	title	innovators	idea_categories	status	description
1	jack	视频注释	jack	1;2	1	视频注释描述
2	jack	优化瀑布模型	jack;jackson	3	0	优化瀑布模型描述
3	jackson	自动化	jackson	1	1	自动化描述
4	jackson	设计模式	jackson	1	0	设计模式描述
5	alice	识别视频对象	alice;jack	2	1	识别视频对象描述
6	bob	Tin Can LMS	bob	1	1	Tin Can LMS 描述
7	bob	文本摘要	bob	2;3	0	文本摘要描述

此表包含 user_info 表中所有注册用户提交的构想的详细信息。

属性

idea_id：- 已提交构想的自动生成 ID
user_id：- 提交构想的用户的 ID。它是一个外键，指向 user_info 表的 'id' 字段
title：- 构想的标题
innovators：- 所有构想贡献者用户 ID 的分号连接列表字符串。例如，innovators ="1;2" 表示，ID 为 1（即 jack）和 ID 为 2（即 jackson）的用户是此构想的贡献者。
idea categories：- 构想可以所属的所有构想类别的类别 ID（指向 idea_categories 表中的 category_id）的分号连接列表字符串。例如，idea_categories ="2;3" 表示该构想属于 ID 为 2（即 Video）和 ID 为 3（即 Language Analysis）的类别。
status：- 构想的接受状态（例如，0 表示待定，1 表示已接受，2 表示已拒绝）
description：- 构想的描述

idea_events

idea_id event_id events

1 1 构想阶段

1 2 实施阶段

3 3 讨论阶段

它包含与 user_idea 表中的构想相关的事件信息。一个构想可以有零个或多个构想事件。
属性
- idea_id：- 指向 user_idea 表的 'idea_id' 字段的外键
- event_id：- 事件的自动生成 ID
- events：- 事件描述字符串
idea_categories

category_id category_name

1 项目生命周期

2 视频

3 语言分析

此表包含所有已注册构想类别的信息，任何提交的构想都可以属于这些类别。
属性
- category_id：- 类别的自动生成 ID
- category_name：- 类别名称
dept_info

id name

1 其他

2 开发

3 管理器

此表列出了所有用户部门。
属性
- id：- 部门的自动生成 ID
- name：- 部门名称

使用代码

下载并解压源代码。将 sql_join.zip 数据库导入您的 MySQL 环境。

 mysql -u root < {path-of-extracted-folder}\sql_join.txt

您还可以使用在线 SQL 练习工具，如 http://sqlfiddle.com/，在线构建您的数据库并对其执行查询。只需将 'sql_join_sqlfiddle.txt' 的内容复制到 sqlfiddle 页面的左侧面板，然后单击 "Build Schema" 按钮。架构构建完成后，您可以在右侧面板输入您的 MySQL 查询，然后单击 "Run SQL" 按钮查看结果显示在底部面板。

好的，让我们开始从系统中获取一些汇总信息，从简单的开始，以探索 inner join 和 inner query 的强大功能。

问题 1：获取带用户信息的构想列表

任务：在此问题中，我们希望检索 user_idea 表中存在的构想及其属性（idea_id、title、status 和 innovators），以及 user_info 表中存在的构想用户信息（user id 和 user full name）。为了获得所需结果，我们需要连接 user_idea 和 user_info 表。

概念：内连接 (Inner Join)，表别名 (Table aliases)

相关表：user_info 和 user_idea

预期结果

idea_id	user_id	full_name	title	innovators	status
1	jack	JACK D.	视频注释	jack	1
2	jack	JACK D.	优化瀑布模型	jack;jackson	0
3	jackson	M.S. Jackson	自动化	jackson	1
4	jackson	M.S. Jackson	设计模式	jackson	0
5	alice	Alice W	识别视频对象	alice;jack	1
6	bob	Bob S.	Tin Can LMS	bob	1
7	bob	Bob S.	另一个构想1	bob	0

解决方案

这是相应的查询

SELECT UI.idea_id, UI.user_id, UInfo.full_name, UI.title, UI.innovators, UI.status
FROM user_idea AS UI 
INNER JOIN user_info AS UInfo ON UI.user_id = UInfo.id

注意：我们在这里使用了 AS 来为表设置别名，以便更方便、更简洁地引用表字段。

这是 Inner Join 用法的一个简单示例。在这里，user_idea 和 user_info 表的记录基于共同的 user_id 进行合并。

在转向复杂查询之前，让我们先尝试理解 SQL 的 'LIKE' 运算符，它主要用于测试一个字符串是否是另一个字符串的一部分。

问题 2：获取特定用户的已接受构想

任务：在这里，我们希望获取一个特定用户（该用户是这些构想的贡献者之一）的所有已接受构想（即 status = 1 的构想）的列表。

概念：使用 Like 运算符检查给定字符串是否是列表中一部分，列表元素使用分隔符（例如 ';'）连接。

相关表：user_idea

预期结果

idea_id	user_id	title	innovators
1	jack	视频注释	jack
5	alice	识别视频对象	alice;jack

解决方案

方法 1

SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND UI.innovators like '%jack%';

结果

idea_id	user_id	title	innovators
1	jack	视频注释	jack
3	jackson	自动化	jackson
5	alice	识别视频对象	alice;jack

问题：如果您仔细检查此查询，会发现它可能会返回错误的结果，如果一个 user_id 是另一个 user_id 的子字符串。例如，上面的查询将返回构想 3，其贡献者为 "jackson"（因为 jackson 包含 jack），这不是我们想要的。这种方法可能适用于每个用户 ID 都不同且不包含其他用户 ID 作为子字符串的情况。

方法 2：在 innovators 字符串的开头和结尾附加列表分隔符（';'）再使用 LIKE 运算符。这是一种匹配 user_id 的技巧性方法，无需在 'LIKE' 语句中使用多个 'OR' 来处理贡献者出现在 innovators 字符串的开头、中间或结尾的情况。

情况 1：附加列表分隔符

SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI  
WHERE UI.status=1 AND CONCAT(';',UI.innovators,';') like '%;jack;%';

或者，我们可以使用以下方法获得相同的结果，但我更喜欢前面的方法，因为它更简洁、更快速。

情况 2：使用多个 'OR'

SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND 
( 
UI.innovators = 'jack'		OR	/* e.g. innovators = 'jack' ; only one user is present in the innovators list*/
UI.innovators like 'jack;%' 	OR 	/* user id is present at the start. e.g. innovators = "jack;bob" */
UI.innovators like '%;jack;%'	OR 	/* user id is present in the middle. e.g. innovators = "alice;jack;bob" */
UI.innovators like '%;jack'   		/* user id is present at the end. e.g. innovators = "alice;jack" */
)

情况 3：使用正则表达式（REGEXP 或 RLIKE）

SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND UI.innovators REGEXP '^jack$|^jack;|;jack;|;jack$';

情况 4：如果该操作频繁使用，可以在数据库中创建一个函数

//Create Function isMember
DELIMITER $$
CREATE FUNCTION IsMember(inList Text, inMember varchar(10))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE exp varchar(50) DEFAULT '';
  SET exp = CONCAT('^',inMember,'$','|^',inMember,';|;','inMember',';|;',inMember,'$');
RETURN inList REGEXP exp;
END
$$
DELIMITER ;

//Using Function
SELECT UI.idea_id, UI.user_id,UI.title, UI.innovators
FROM user_idea AS UI 
WHERE UI.status=1 AND IsMember(UI.innovators,'jack');

性能分析：通常，对于大型数据集，'LIKE' 比 'REGEXP'（此处为函数）更优化。以下是针对约 70K user_ideas 数据集的查询的近似执行时间。

系统详情

处理器：(Intel Core i5 CPU M520 @2.4GHz 2.4GHz)，操作系统：(Windows 7, 4GB RAM)，MySQL 版本：(5.5.24 for Win32 (x86))。

查询执行时间

情况 1 (CONCAT with LIKE)：~0.28 秒
情况 2 (Multiple LIKE with OR)：~0.29 秒
情况 3 (REGEXP)：~0.9 秒
情况 4 (FUNCTION)：~8.6 秒

结果

idea_id	user_id	title	innovators
1	jack	视频注释	jack
5	alice	识别视频对象	alice;jack

问题 3：获取带构想类别名称和构想事件信息的构想详情

任务：这个问题有点复杂，因为它涉及从一个表（例如 idea_events 表）获取连接后的数据（例如构想事件），并将其与从另一个表（例如 idea 表）获取的行合并。

概念：左连接 (LEFT JOIN)，内连接 (INNER JOIN)，嵌套 SQL 查询，表别名，以及聚合函数如 GROUP BY、GROUP_CONCAT。

相关表：user_idea, idea_events, idea_categories

预期结果

idea_id	title	events	categories
1	视频注释	构想阶段;实施阶段	项目生命周期;视频
2	优化瀑布模型	NULL	语言分析
3	自动化	讨论阶段	项目生命周期
4	设计模式	NULL	项目生命周期
5	识别视频对象	NULL	视频
6	Tin Can LMS	NULL	项目生命周期
7	文本摘要	NULL	视频;语言分析

解决方案

我们可以将问题分为两部分。第一，我们将获取与每个构想相关的所有事件，并使用某个分隔符（例如 ';'）将它们合并到一个连接字段（命名为 'events'）中。第二，我们将此查询的结果与 idea_categories 表连接，以添加构想类别信息。

注意：构想事件可能为 NULL。也就是说，有些构想可能没有任何相关的事件。

步骤 1：获取构想事件的查询（使用分隔符 ';' 连接）

方法 1

	SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events 
	FROM user_idea AS UI 
	INNER JOIN idea_events AS IE ON UI.idea_id = IE.idea_id      
	GROUP BY UI.idea_id

注意：GROUP BY 和 GROUP_CONCAT 聚合运算符用于将单个构想的所有事件分组。

结果

idea_id	title	events
1	视频注释	构想阶段;实施阶段
3	自动化	讨论阶段

问题：由于使用了 INNER JOIN，没有相关事件的构想未显示在结果中。我们可以在方法 2 中使用 LEFT JOIN 来解决这个问题。

方法 2

	SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events 
	FROM user_idea UI 
	LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id      
	GROUP BY UI.idea_id

结果

idea_id	title	events
1	视频注释	构想阶段;实施阶段
2	优化瀑布模型	NULL
3	自动化	讨论阶段
4	设计模式	NULL
5	识别视频对象	NULL
6	Tin Can LMS	NULL
7	文本摘要	NULL

注意

我们使用了 LEFT JOIN 而不是 INNER JOIN，因为即使没有对应的事件，我们也希望获取所有构想的记录。
我们使用 GROUP_CONCAT 和 GROUP_BY 将每个构想的事件合并到单个条目中。如果没有 GROUP_BY 和 GROUP_CONCAT 运算符，我们将为每个构想获得多个条目，如下所示（参见结果的前两行）。
```
	SELECT UI.idea_id, UI.title, IE.events
	FROM user_idea UI 
	LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id;	
		
```
结果（不使用 GROUP_BY）

idea_id title events

1 视频注释构想阶段

1 视频注释实施阶段

2 优化瀑布模型 NULL

... ... ...

但是，如果我们使用 GROUP_BY 而不使用 GROUP_CONCAT，我们将无法获得预期的结果。我们将获得每个构想的一行，其中包含与该构想对应的单个事件信息（缺少其他事件），因为我们没有使用 GROUP_CONCAT 来合并事件（请参阅结果第 1 行的事件列。只显示了 'Ideation Phase' 而不是 'Implementation Phase'）。关键规则是，在使用 GROUP_BY 时，应该使用 GROUP_CONCAT、AVG、SUM、COUNT、MAX、MIN 等聚合运算符。
```
	SELECT UI.idea_id, UI.title, IE.events  
	FROM user_idea UI 
	LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id
	GROUP BY UI.idea_id;
		
```
结果（不使用 GROUP_CONCAT）

idea_id title events

1 视频注释构想阶段

2 优化瀑布模型 NULL

3 自动化讨论阶段

... ... ...

步骤 2：通过将 idea_categories 表与步骤 1 中的查询结果连接来获取类别信息。

我们将步骤 1 中的 SQL 查询用作内部或嵌套查询来获取类别信息。

SELECT AUI.idea_id, AUI.title, AUI.events,
       GROUP_CONCAT(IC.category_name SEPARATOR ';') as categories  
FROM (       
	SELECT UI.idea_id, UI.title, GROUP_CONCAT(IE.events SEPARATOR ';') as events ,
	       CONCAT(';',UI.idea_categories,';') as temp_categories
 	FROM user_idea UI 
	LEFT JOIN idea_events IE ON UI.idea_id = IE.idea_id      
	GROUP BY UI.idea_id
) AS AUI
INNER JOIN idea_categories IC
ON AUI.temp_categories LIKE CONCAT('%;',IC.category_id,';%') 
GROUP BY AUI.idea_id;

结果

idea_id	title	events	categories
1	视频注释	构想阶段;实施阶段	项目生命周期;视频
2	优化瀑布模型	NULL	语言分析
3	自动化	讨论阶段	项目生命周期
4	设计模式	NULL	项目生命周期
5	识别视频对象	NULL	视频
6	Tin Can LMS	NULL	项目生命周期
7	文本摘要	NULL	视频;语言分析

注意：

我们使用了别名为 AUI 的嵌套查询结果集来附加类别信息。通常，当您想对某些临时或中间 SQL 查询的结果集执行连接操作时，以下模式的嵌套查询非常有用。
```
	SELECT T1.field1,..., NT.field1,... 
	FROM ( 
		SELECT T2.field1, ..
		FROM T2
		WHERE ...
	) AS NT /* Nested Query Result */
	INNER JOIN T1 ON T1.someField = NT.someField && ....
	WHERE ...
	
```
我们应用了问题 2（方法 2 - 情况 1）中提到的技术来获取类别名称（这次我们在 SELECT 语句中使用了相同的技术）。但是，我们也可以在条件子句中使用它）。在嵌套查询中，我们向 user_idea.categories 字段附加列表分隔符（';'），并将其别名为 'temp_categories'，该别名将在外部查询的条件子句中使用。嵌套查询的结果集将包含 4 列，即 'idea_id'、'title'、'events'（连接的事件字符串）和 'temp_categories'。现在，此结果集用作别名为 'AUI' 的临时表，然后通过 on temp_categories 的 LIKE 条件与 idea_categories 表进行连接。然后在外部 SELECT 查询中使用 GROUP_BY 和 GROUP_CONCAT 来获取构想类别名称的连接字符串。
获得相同结果的另一种方法是，分别获取构想事件和构想类别，并使用内连接或交集。

问题 4：根据某些启发式（例如组平均分）获取顶尖贡献者组。

任务：在此问题中，我们希望获取领先或顶尖组的信息，包括诸如领先组的总贡献者数、该组贡献者提交的总构想数以及总组积分等属性。此外，根据组平均分对结果进行排序。

概念：COUNT，SUM，GROUP BY，ORDER BY，DESC，嵌套 SQL 查询，JOIN。

相关表：user_info, user_idea, dept_info

预期结果

dept_id	dept_name	totalInnovators	totalIdeas	totalPoints
3	管理器	1	2	3
2	开发	3	5	5

解决方案

我们将此任务也分为两部分，如问题 3 所述。首先，我们将 user_info 与 dep_info 连接以获取用户部门或组信息，并将其与 idea 表连接以获取每个用户提交的总构想数。其次，我们将上一步的结果与 dept_info 连接，使用聚合运算符获取每个组的总贡献者数、总构想数、总积分，并按组平均分排序。

步骤 1：通过连接 user_pre_info 和 dept_info 获取用户部门信息，并计算用户构想数。

SELECT UPI.id as user_id, UPI.dept_id, DI.name as dept_name, UPI.points,                          COUNT(UI.idea_id) AS totalIdeas 
FROM user_info UPI 	
INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id 
LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id 
GROUP BY UPI.id

结果

user_id	dept_id	dept_name	points	totalIdeas
alice	2	开发	1	1
bob	2	开发	2	2
jack	2	开发	2	2
jackson	3	管理器	3	2

注意

我们使用 COUNT 运算符来获取每个用户的构想计数。
我们使用 GROUP BY 运算符按 user_id 进行分组。

步骤 2：计算组中的用户数、总构想数，并按每个组的用户数对应的平均分对结果进行排序。

我们将步骤 1 中 SQL 查询的结果与 dept_info 表连接以获得所需的结果。

SELECT UGI.dept_id, UGI.dept_name, COUNT(UGI.id) AS totalInnovators,                              SUM(UGI.totalIdeas) AS totalIdeas, SUM(UGI.points) AS totalPoints 
FROM ( 
        SELECT UPI.id, UPI.dept_id, DI.name as dept_name, UPI.points,                                     COUNT(UI.idea_id) AS totalIdeas 
	FROM user_info AS UPI 
	INNER JOIN dept_info AS DI ON UPI.dept_id=DI.id 
	LEFT JOIN user_idea AS UI ON UPI.id = UI.user_id 
	GROUP BY UPI.id
) AS UGI 
GROUP BY UGI.dept_id HAVING (totalPoints>0 AND totalInnovators>0) ORDER BY SUM(UGI.points)/COUNT(UGI.id) DESC LIMIT 5

结果

dept_id	dept_name	totalInnovators	totalIdeas	totalPoints
3	管理器	1	2	3
2	开发	3	5	5

注意

我们按部门 ID 对结果进行分组，以获得每个组的总积分。
我们使用 ORDER BY 按组平均分对结果进行排序。

id	full_name	dep_id	points
jack	JACK D.	2	2
jackson	M.S. Jackson	3	3
alice	Alice W	2	1
bob	Bob S.	2	2

idea_id	event_id	events
1	1	构想阶段
1	2	实施阶段
3	3	讨论阶段

category_id	category_name
1	项目生命周期
2	视频
3	语言分析

id	name
1	其他
2	开发
3	管理器