一对多表关联的SQL查询：筛选符合特定通知条件的用户

嘿，这就帮你搞定这两个SQL查询需求！先理清楚咱们手上的表数据：

现有表结构与数据 #

Users表 #

Notifications表 #

场景1：筛选同时具备(NotifType=0且NotifValue=1)、(NotifType=1且NotifValue=100)的用户 #

这里有两种常用的写法，都能精准拿到你要的结果：

方法1：自连接（直观易懂） #

把Notifications表和自己连两次，分别匹配两个条件，再关联Users表取用户名：

SELECT DISTINCT u.Name
FROM Users u
JOIN Notifications n1 ON u.ID = n1.UserId
JOIN Notifications n2 ON u.ID = n2.UserId
WHERE 
  n1.NotifType = 0 AND n1.NotifValue = 1
  AND n2.NotifType = 1 AND n2.NotifValue = 100;

方法2：分组聚合（扩展性强） #

先筛选出符合两个条件之一的记录，再分组统计用户是否同时满足两个条件：

SELECT u.Name
FROM Users u
JOIN Notifications n ON u.ID = n.UserId
WHERE 
  (n.NotifType = 0 AND n.NotifValue = 1)
  OR (n.NotifType = 1 AND n.NotifValue = 100)
GROUP BY u.ID, u.Name
HAVING COUNT(DISTINCT CONCAT(n.NotifType, '-', n.NotifValue)) = 2;

两种写法都会返回：John、Steve。

场景2：筛选同时具备(NotifType=0且NotifValue=1)、(NotifType=1且NotifValue=101)的用户 #

和场景1逻辑一致，只需要调整第二个条件的NotifValue就行：

自连接写法 #

SELECT DISTINCT u.Name
FROM Users u
JOIN Notifications n1 ON u.ID = n1.UserId
JOIN Notifications n2 ON u.ID = n2.UserId
WHERE 
  n1.NotifType = 0 AND n1.NotifValue = 1
  AND n2.NotifType = 1 AND n2.NotifValue = 101;

分组聚合写法 #

SELECT u.Name
FROM Users u
JOIN Notifications n ON u.ID = n.UserId
WHERE 
  (n.NotifType = 0 AND n.NotifValue = 1)
  OR (n.NotifType = 1 AND n.NotifValue = 101)
GROUP BY u.ID, u.Name
HAVING COUNT(DISTINCT CONCAT(n.NotifType, '-', n.NotifValue)) = 2;

这个查询会返回预期结果：John。

小提示 #

• 自连接适合条件较少的场景，逻辑一眼就能看明白；
• 分组聚合的优势在于扩展性，如果以后要加更多条件，只需要在WHERE里加OR语句，同时把HAVING里的数字改成对应的条件数量就行；
• 加DISTINCT是为了避免同一个用户因为有多条符合条件的记录而重复出现在结果里~

内容的提问来源于stack exchange，提问作者Samuel Kazan