对 4 个表的 SQL 查询：非常复杂？

Question

我有四个表

alert:
    * monitor_id
    * group_id

group:
    * group_id

user:
    * user_id
    * email

user_group:
    * group_id
    * user_id

是否可以仅使用 monitor_id 来获取用户的邮件？ 如果是！是否可以只在一个查询中进行？ 请帮助我无论是通过sql查询还是通过主义！

Answer 1

正确的查询：

SELECT DISTINCT user.email FROM user 
inner join user_group on user.user_id=user_group.user_id 
inner join alert on  alert.group_id=user_group.group_id
where alert.monitor_id = provided_monitor_id

上一个带有 Shahbaz 评论的查询。

SELECT * FROM user inner join user_group on user.user_id=user_group.user_id 
inner join  group on user_group.group_id=group.group_id inner 
join alert on  alert.group_id=group.group_id
group by user.user_id

编辑（作者：Shahbaz）：为了帮助解释该命令的工作原理：

当您有两个表共享一个字段（如果您愿意，可以是一个变量）时，您可以 join 这两个表（也称为 inner join），从而产生一个更大的表，合并两个表中的数据（如果没有共享字段，您甚至可以这样做）。让我们通过一个例子来了解一下：

表 People：字段：pid、pname、pjob，

其中包含条目

• (0, 'Alice', 'Student')
• (1, 'Bob', '工厂工人')
• (2, 'clara', '护士'）

表工作：字段：jname，jpay，

包含条目

• （'老师'，1000）
• （'学生'，0）
• （'工厂工人'， 2000)

现在，如果您说 People join Jobs 您会得到一个包含 9 个条目的表，其中包含：

字段：pid、pname、pjob、jname、jpay，

其中条目为

• (0、'Alice'、'student'、 '老师', 1000)
• (0, '爱丽丝', '学生', '学生', 0)
• (0, '爱丽丝', '学生', '工厂工人', 2000)
• (1, '鲍勃', '工厂工人', '老师', 1000)
• (1, '鲍勃', '工厂工人', '学生', 0)
• (1, '鲍勃'，'工厂工人'，'工厂工人'，2000)
• (2，'克拉拉'， '护士', '老师', 1000)
• (2, '克拉拉', '护士', '学生', 0)
• (2, '克拉拉', '护士', '工厂工人', 2000)

正如你所看到的还不是这么好的桌子！您可以使用join 中的on 条件来提取彼此相关的信息。例如，我们可能想查看每个人的工资。因此，People.pjob 应该等于 Jobs.jname，因此，如果您说 People join Jobs on People.pjob=Jobs.jname 您会得到一个表，其中包含：

字段：pid、pname、pjob、jname , jpay

包含条目

• (0, 'Alice', '学生', '学生', 0)
• (1, '鲍勃', '工厂工人', '工厂工人', 2000)

请注意，Clara 的工作不存在于 Jobs 表中，因此她不存在于最终表中。

虽然内连接为您提供来自两个匹配表的数据，但您可能希望获取表的数据，即使另一端没有匹配的数据。这称为外连接。在此示例中，People 外连接 Jobs 生成一个包含字段的表

：pid、pname、pjob、jname、jpay，

其中条目为

• (0, 'Alice', 'student', 'student', 0)
• (1 , '鲍勃', '工厂工人', '工厂工人', 2000)
• (2, '克拉拉', '护士', null, null)
• (null, null, null, 'teacher', 1000)

如果您只想要表即使不匹配，也要包含 outer join 的左侧，请使用 left external join （在这种情况下，将出现“Clara”行，而不是“teacher”行） ' 排）。同样，右外连接会给出右表中的行（即使不匹配），但不会给出左表中的行。

最后，您必须已经知道 select 的作用，它只是返回一个表，其中包含从提供的列中选择的列（* 选择所有列）。给定的表可以只是表的名称，也可以是使用 sql 命令形成的任何其他表（使用 select 命令本身创建的表（在这种情况下，请毫不犹豫地使用括号），使用加入命令等）。如果您了解 SQL 的递归性质，您就可以创建任意复杂的命令。想象一下，表上的每个操作都会生成另一个表，您可以简单地与其他操作结合使用，就好像该表是一个简单的存储命名表一样！

Answer 2

如果您想使用 Monitor_id 获取用户电子邮件，请执行以下操作：

SELECT DISTINCT
  u.user_id
  u.email
FROM `user` u
INNER JOIN user_group ug ON u.user_id = ug.user_id 
INNER JOIN `group` g ON ug.group_id = g.group_id 
INNER JOIN alert a ON a.group_id = g.group_id
WHERE a.monitor_id = '1548'

因为从 Monitor_id 到 user.email 的路径会遍历所有表，所以您将需要所有这些联接。
只要您对连接中使用的所有字段都建立了索引（！），运行时应该会很快。

Answer 3

另一种方法（不使用DISTINCT或GROUP BY）：

SELECT 
    u.user_id
  , u.email
FROM `user` AS u
WHERE EXISTS
      ( SELECT *
        FROM alert AS a 
          JOIN `group` AS g
            ON g.group_id = a.group_id 
          JOIN user_group AS ug
            ON ug.group_id = g.group_id
        WHERE ug.user_id = u.user_id 
          AND a.monitor_id = @monitorId      -- the monintor_id to be checked
      ) 

---

注意：使用关键字或保留字命名的表或字段不是一个好习惯用户，甚至更糟糕的组。