使用具有一对多关系的联接来最小化SQL查询

问题描述

因此，让我以任何方式说我都不是SQL向导作为开头.我想做的是一个简单的概念，但是在尝试最小化我正在执行的数据库查询量时给我带来了一个小挑战.

So let me preface this by saying that I'm not an SQL wizard by any means. What I want to do is simple as a concept, but has presented me with a small challenge when trying to minimize the amount of database queries I'm performing.

假设我有一个部门表.每个部门内都有员工列表.

Let's say I have a table of departments. Within each department is a list of employees.

列出所有部门以及每个部门中有哪些雇员的最有效方法是什么.

What is the most efficient way of listing all the departments and which employees are in each department.

例如，如果我的部门表包含以下内容:

So for example if I have a department table with:

id   name
1    sales
2    marketing

还有一个人桌，其中:

id   department_id   name
1    1               Tom
2    1               Bill
3    2               Jessica
4    1               Rachel
5    2               John

最好的方法是像这样列出所有部门和每个部门的所有员工:

What is the best way list all departments and all employees for each department like so:

销售

• 汤姆
• 比尔
• Rachel

营销

• 杰西卡
• 约翰

假设两个表实际上都是巨大的. (我想避免获得部门列表，然后循环遍历结果并为每个部门进行单独查询).当状态和评论存储在单独的表中时，可以类似地考虑在类似Facebook的系统中选择状态/评论.

Pretend both tables are actually massive. (I want to avoid getting a list of departments, and then looping through the result and doing an individual query for each department). Think similarly of selecting the statuses/comments in a Facebook-like system, when statuses and comments are stored in separate tables.

推荐答案

您可以通过简单的连接在单个查询中获得全部信息，例如:

You can get it all in a single query with a simple join, e.g.:

SELECT   d.name AS 'department', p.name AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
ORDER BY department

这将返回所有数据，但是使用起来有些麻烦，因为无论如何您都必须遍历每个人.您可以进一步将它们分组在一起:

This returns all the data, but it's a bit of a pain to consume, since you'll have to iterate through every person anyway. You can go further and group them together:

SELECT   d.name AS 'department',
         GROUP_CONCAT(p.name SEPARATOR ', ') AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
GROUP BY department

您将获得类似以下内容的输出:

You'll get something like this as the output:

department | name
-----------|----------------
sales      | Tom, Bill, Rachel
marketing  | Jessica, John

这篇关于使用具有一对多关系的联接来最小化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！