SQL语法错误：')'附近语法不正确，请求查询代码修改方案

错误根源分析 #

• 第二层子查询（别名AnyNameYouLikeButYouHaveToGiveOne）的GROUP BY子句中，错误引用了原表列名（如att.work_loc、vEmpDtl.emp_Code），但该子查询的SELECT已为这些列定义别名，必须使用别名或子查询SELECT中的列名。
• 第二层子查询的GROUP BY包含了out_ts2，但内层子查询的SELECT未输出该列，属于无效引用。
• 最外层GROUP BY引用了ECount1、ECount2，但上一层子查询的结果集仅输出EmpCount，无这两个列，导致语法错误。
• 最内层子查询使用聚合函数count(in_Ts)、count(in_Ts2)却未添加GROUP BY，会导致聚合结果是整个数据集的计数，而非按业务维度（员工/日期）分组的统计，不符合逻辑。

修正后的完整SQL代码 #

select  
    attnDate, workLoc, swipeType, fromTime, toTime, timeInterval,  
    sum(EmpCount) as TotalEmpCount, 
    EmpCode 
from
    (select
         attnDate, workLoc, 
         EmpCode,  
         swipeType, 
         sum(ECount1 + ECount2) as EmpCount,
         fromTime, toTime, timeInterval
     from
         (select
              att.attn_dt as attnDate, att.work_loc as workLoc, 
              vEmpDtl.emp_Code as EmpCode,  
              case 
                  when convert(time, att.in_Ts) between '7:00' and '10:30' then 'IN'   
                  when convert(time, att.in_Ts2) between '7:00' and '10:30' then 'IN'  
                  else ' ' 
              end as swipeType, 
              count(att.in_Ts) as ECount1, count(att.in_Ts2) as ECount2,
              td.from_time as fromTime, td.to_time as toTime, 
              td.time_interval as timeInterval
          from 
              emp_attn att 
          cross join 
              time_details td  
          inner join 
              v_emp_dtls vEmpDtl on att.emp_code = vEmpDtl.emp_code
          join 
              emp_mst mst with (nolock) on mst.emp_code = vEmpDtl.emp_code  
          join 
              (select distinct empRep.emp_code  
               from emp_reporting_structure empRep  
               where empRep.report_to = '18501') o on o.emp_code = vEmpDtl.emp_code 
          where
              att.attn_dt between '2023-02-01' and '2023-02-28'  
              and vEmpDtl.emp_type = 'COMMON'  
              and vEmpDtl.emp_loc_type = 'KSACORP' 
              and vEmpDtl.work_loc_id = '300000001672100'  
              and vEmpDtl.nationality = 'Sri Lanka'  
              and vEmpDtl.payroll_code = 'KSA Retail Payroll'  
              and vEmpDtl.country_code = '6486' 
              and vEmpDtl.region = 'RIY' 
              and (convert(time, att.in_Ts) between convert(time, td.from_time) and convert(time, td.to_time) or
                   convert(time, att.in_Ts2) between convert(time, td.from_time) and convert(time, td.to_time))  
              and (convert(time, att.in_Ts) between '7:00' and '10:30' or  
                   convert(time, att.in_Ts2) between '7:00' and '10:30') 
              and vEmpDtl.emp_code not in (select distinct emp_code  
                                           from exclude_emp 
                                           where exclusion_type = 'NO_ATTENDANCE')
              and mst.contract is null  
              and vEmpDtl.employment_status = 'All'  
          group by 
              att.attn_dt, att.work_loc, vEmpDtl.emp_Code,
              td.from_time, td.to_time, td.time_interval,
              case 
                  when convert(time, att.in_Ts) between '7:00' and '10:30' then 'IN'   
                  when convert(time, att.in_Ts2) between '7:00' and '10:30' then 'IN'  
                  else ' ' 
              end
         ) InnerQuery 
     group by 
         attnDate, workLoc, EmpCode, swipeType, fromTime, toTime, timeInterval) aaa 
group by 
    attnDate, timeInterval, workLoc, swipeType, fromTime, toTime, EmpCode 

关键修改说明 #

• 为最内层子查询添加GROUP BY，按日期、工作地点、员工编码、时间区间及刷卡类型分组，确保count函数按业务维度统计。
• 第二层子查询的GROUP BY统一使用内层子查询的列别名，避免引用原表列名导致的语法错误。
• 移除所有GROUP BY中不存在于对应子查询SELECT中的列（如out_ts2、ECount1、ECount2）。
• 调整聚合逻辑，将sum(ECount1+ECount2)移到第二层子查询，最外层仅对已聚合的EmpCount再次求和，同时移除无效的列引用。

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