如何编写单条SQL/ActiveRecord查询获取参训人员及特定技能拥有情况？

嘿，这个需求太常见了，咱们直接用LEFT OUTER JOIN就能完美解决，完全没必要用Union或者给每个员工跑子查询，既高效又简洁。

先给你明确下思路：我们需要把目标培训的所有参训人员拉出来，同时关联他们的技能记录——但只过滤你指定的那个特定技能，通过LEFT JOIN的特性，有这个技能的用户会关联到对应的技能记录，没有的就会返回NULL，最后用CASE语句把NULL转成"no"，非NULL转成"yes"就行。

假设的表结构说明 #

先默认咱们的数据库表是这样的（如果你的表名/字段名不一样，对应替换就行）：

• trainings：培训表，存培训的基本信息，主键id
• training_participants：参训关系表，关联training_id和user_id
• users：用户表，存员工姓名，主键id，字段name
• user_skills：用户技能表，关联user_id和skill_id（或者直接有skill_name字段）

原生SQL版本 #

SELECT 
  u.name AS participant_name,
  -- 判断是否关联到了目标技能记录
  CASE WHEN us.skill_id IS NOT NULL THEN 'yes' ELSE 'no' END AS has_this_one_specific_skill
FROM trainings t
-- 关联参训关系表，只取目标培训的人员
JOIN training_participants tp ON t.id = tp.training_id
-- 关联用户表拿姓名
JOIN users u ON tp.user_id = u.id
-- LEFT JOIN技能表，并且只匹配目标技能
LEFT OUTER JOIN user_skills us 
  ON u.id = us.user_id 
  AND us.skill_id = 123; -- 这里替换成你的特定技能ID，或者用us.skill_name = 'Python编程'这种文本匹配
-- 指定要查询的培训
WHERE t.id = 456; -- 替换成你的目标培训ID

ActiveRecord（Rails）版本 #

假设你的模型关联已经设置好了：

• Training模型：has_many :training_participants; has_many :users, through: :training_participants
• User模型：has_many :user_skills
• UserSkill模型：belongs_to :user; belongs_to :skill

写法如下（两种方式，选顺手的就行）：

方式1：直接指定JOIN条件

# 替换成你的目标培训ID和特定技能ID
target_training_id = 456
specific_skill_id = 123

Training.find(target_training_id).users
  .select(
    "users.name AS participant_name",
    "CASE WHEN user_skills.id IS NOT NULL THEN 'yes' ELSE 'no' END AS has_this_one_specific_skill"
  )
  .left_outer_join(:user_skills) do |join|
    join.on(user_skills: { user_id: users[:id], skill_id: specific_skill_id })
  end

方式2：用merge简化条件

# 先找到特定技能对象
specific_skill = Skill.find_by(name: "Python编程")
target_training = Training.find(456)

target_training.users
  .select(
    "users.name AS participant_name",
    "CASE WHEN user_skills.id IS NOT NULL THEN 'yes' ELSE 'no' END AS has_this_one_specific_skill"
  )
  .left_outer_join(:user_skills)
  .merge(UserSkill.where(skill: specific_skill))

为什么不用Union？ #

Union是用来合并两个独立的结果集（比如先查有技能的参训人员，再查没技能的），但这种方式需要写两个查询，还要处理结果集的字段对齐，反而多此一举。用LEFT JOIN只需要一次查询就能把所有参训人员的信息和技能状态都拉出来，效率更高，代码也更易维护。

这样查出来的结果直接遍历就能生成你要的表格啦，完全符合你的需求~

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