一对多关系下的SQL查询编写:指定视频筛选及关联字段合并方法
关联查询前提约定
- 默认表关联逻辑:
users表以id为主键存储用户信息;videos表以user_id为外键关联用户表,存储视频条目,视频名称字段为name - 两个查询的公共筛选条件:视频名称严格匹配
"Video1"或"Video2"
筛选匹配视频的用户名称
一对多关联下,单个用户如果同时拥有2条符合条件的视频,直接连表查询会返回重复的用户记录,需要做去重处理,根据数据量级可以选以下两种写法:
-- 写法1:连表加DISTINCT去重,适合用户、视频表数据量适中的场景 SELECT DISTINCT u.name FROM users u JOIN videos v ON u.id = v.user_id WHERE v.name IN ('Video1', 'Video2'); -- 写法2:EXISTS子查询,适合视频表数据量大的场景,性能更优 SELECT u.name FROM users u WHERE EXISTS ( SELECT 1 FROM videos v WHERE v.user_id = u.id AND v.name IN ('Video1', 'Video2') );
注意:不要直接用
WHERE u.id IN (SELECT user_id FROM videos WHERE ...)的简化写法,当视频表存在同一个用户多条匹配记录时,部分数据库版本会触发返回行数偏移、隐式重复行的问题。
返回匹配用户的名称+合并后的对应用户符合条件的视频列表
因为是一对多关系,要让同一个用户只占一行,需要用聚合函数把该用户名下所有符合条件的视频值合并为单个字段,不同数据库的对应写法如下:
-- MySQL 5.7+ 版本写法,默认逗号分隔合并值 SELECT u.name AS user_name, GROUP_CONCAT(v.name SEPARATOR ',') AS matched_videos FROM users u JOIN videos v ON u.id = v.user_id WHERE v.name IN ('Video1', 'Video2') GROUP BY u.id, u.name; -- PostgreSQL 写法 SELECT u.name AS user_name, STRING_AGG(v.name, ',') AS matched_videos FROM users u JOIN videos v ON u.id = v.user_id WHERE v.name IN ('Video1', 'Video2') GROUP BY u.id, u.name; -- SQL Server 2017+ 版本写法 SELECT u.name AS user_name, STRING_AGG(v.name, ',') AS matched_videos FROM users u JOIN videos v ON u.id = v.user_id WHERE v.name IN ('Video1', 'Video2') GROUP BY u.id, u.name;
注意:分组时优先按用户主键
u.id聚合,不要只按u.name分组——实际业务中重名用户是普遍存在的,只按名称分组会把不同用户的视频错误合并。如果需要去掉合并结果里的重复视频值,可以在聚合函数内加DISTINCT关键字,例如GROUP_CONCAT(DISTINCT v.name SEPARATOR ',')。
内容的提问来源于stack exchange,提问作者miclofa
相关产品推荐
相关产品推荐

