基于给定关系模式的SQL查询作答校验及正确实现指导
a题作答校验与修正
原提交代码的核心错误
- 分组维度不符合统计目标:子查询按
CodG, City分组,未将教练SSN纳入分组键,在标准SQL规范下该写法属于非法查询(查询返回的SSN字段既不是分组列,也不是聚合计算结果),无法实现“按教练维度统计覆盖健身房数量”的核心要求 - 聚合条件错误:原代码
count=3既未明确聚合的统计对象,也不符合题目“至少3家健身房”的语义(要求判断阈值为≥3,不是恰好等于3) - 统计口径偏差:未对关联的健身房做去重处理,若同一教练在同一家健身房多次开课,会被重复计数,无法准确统计覆盖的独立健身房数量
修正后的正确SQL实现
SELECT t.SSN, t.Name, t.Surname FROM TRAINER t WHERE t.SSN IN ( SELECT gl.SSN FROM GROUP_LESSON gl INNER JOIN GYM g ON gl.CodG = g.CodG WHERE g.City = 'Turin' GROUP BY gl.SSN HAVING COUNT(DISTINCT gl.CodG) >= 3 )
b题实现思路与正确SQL
拆解实现逻辑
- 第一步筛选目标健身房:关联团课表与专项表,筛选专项为Karate的所有团课记录,按健身房编码分组,保留Karate开课总节数>10的健身房
- 第二步聚合统计:对筛选出的健身房,按“健身房编码+教练SSN”维度分组,累计计算每个教练在对应健身房内所有专项团课的参与人数总和
正确SQL实现(采用CTE结构降低阅读门槛)
WITH eligible_gyms AS ( -- 筛出符合Karate开课要求的健身房 SELECT gl.CodG FROM GROUP_LESSON gl INNER JOIN SPECIALTY s ON gl.CodS = s.CodS WHERE s.NameS = 'Karate' GROUP BY gl.CodG HAVING COUNT(*) > 10 ) SELECT gl.CodG, gl.SSN, t.Name, t.Surname, SUM(gl.ParticipantsNumber) AS total_participants FROM GROUP_LESSON gl INNER JOIN eligible_gyms eg ON gl.CodG = eg.CodG INNER JOIN TRAINER t ON gl.SSN = t.SSN GROUP BY gl.CodG, gl.SSN, t.Name, t.Surname
c题实现思路与正确SQL
拆解实现逻辑
- 核心判断规则:对每个私教,先统计其常住城市的全部健身房总数,再统计该私教在自己城市实际开过团课的去重健身房数量,两个数值完全相等即代表该教练覆盖了所在城市的所有健身房
- 统计要求:对满足覆盖条件的私教,计算其开过的所有不同专项的总数量
正确SQL实现
WITH trainer_coverage_stats AS ( SELECT t.SSN, t.Name, t.Surname, -- 统计私教所在城市的全部健身房总数 COUNT(DISTINCT g_all.CodG) AS total_gyms_in_city, -- 统计私教在自己城市实际开过课的去重健身房数量 COUNT(DISTINCT CASE WHEN gl.SSN IS NOT NULL THEN g_all.CodG END) AS covered_gym_count, -- 统计私教开过的不同专项总数 COUNT(DISTINCT gl.CodS) AS total_specialty_count FROM TRAINER t -- 关联私教所在城市的所有健身房,内连接保证不遗漏任何一家城市内的健身房 INNER JOIN GYM g_all ON t.City = g_all.City -- 左连接团课表,匹配教练在对应健身房的开课记录 LEFT JOIN GROUP_LESSON gl ON gl.SSN = t.SSN AND gl.CodG = g_all.CodG GROUP BY t.SSN, t.Name, t.Surname ) SELECT Name, Surname, total_specialty_count FROM trainer_coverage_stats WHERE covered_gym_count = total_gyms_in_city
内容的提问来源于stack exchange,提问作者Ani Lici
相关产品推荐
相关产品推荐

