SQL子查询报错：无效对象名'group1-2'，求修复方案

执行以下SQL代码时出现错误提示：Invalid object name 'group1-2'，问题根源及修复方案如下：

原错误代码 #

select
  CASE
    WHEN reference_s = 'R1' THEN
      (select sum(DATEDIFF(second, datetime2_s,datetime6_s) ) from group1_2)
    WHEN reference_s = 'R2' THEN
      (select sum(DATEDIFF(second, datetime3_s,datetime6_s) ) from group1_2)
  END
  / (CAST(count(*) AS float)*60)
from
(
  select * from
  (
    select
      id,
      min(reference) as reference_s, min(datetime2) as datetime2_s,
      min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
      count(*) as type_s, sum(size) as size_s
    from dbo.testing
    group by id
  ) group1
  where type_s=2 and cntr_size_s=44
  UNION
  select * from
  (
    select
      id,
      min(reference) as reference_s, min(datetime2) as datetime2_s,
      min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
      count(*) as type_s, sum(size) as size_s
    from dbo.testing
    group by id
  ) group2
  where type_s=1
) group1_2
where datetime6_s IS NOT null
and datetime6_s >= '2023-03-01 00:00'
and datetime6_s <= '2023-03-30 23:59'

问题原因 #

1. 作用域限制：CASE语句内的嵌套子查询无法直接引用外层的派生表别名group1_2，SQL解析器会将其视为独立的数据库对象（表/视图），而非外层临时结果集。
2. 笔误问题：原代码中where type_s=2 and cntr_size_s=44里的cntr_size_s是错误别名，派生表中对应的字段是size_s。

修复后的代码 #

-- 先计算全局总和，再进行CASE分支计算
select
  CASE
    WHEN reference_s = 'R1' THEN total_diff_r1
    WHEN reference_s = 'R2' THEN total_diff_r2
  END
  / (CAST(total_count AS float)*60)
from
(
  -- 外层派生表，同时计算全局的两个时间差总和和总记录数
  select
    *,
    sum(DATEDIFF(second, datetime2_s, datetime6_s)) over() as total_diff_r1,
    sum(DATEDIFF(second, datetime3_s, datetime6_s)) over() as total_diff_r2,
    count(*) over() as total_count
  from
  (
    select * from
    (
      select
        id,
        min(reference) as reference_s, min(datetime2) as datetime2_s,
        min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
        count(*) as type_s, sum(size) as size_s
      from dbo.testing
      group by id
    ) group1
    where type_s=2 and size_s=44  -- 修正别名笔误
    UNION
    select * from
    (
      select
        id,
        min(reference) as reference_s, min(datetime2) as datetime2_s,
        min(datetime3) as datetime3_s, max(datetime6) as datetime6_s,
        count(*) as type_s, sum(size) as size_s
      from dbo.testing
      group by id
    ) group2
    where type_s=1
  ) group1_2
  where datetime6_s IS NOT null
  and datetime6_s >= '2023-03-01 00:00'
  and datetime6_s <= '2023-03-30 23:59'
) temp

关键改动说明 #

• 使用窗口函数over()直接在派生表内计算全局的时间差总和和总记录数，避免跨作用域引用派生表别名的问题。
• 修正了cntr_size_s为正确的字段别名size_s。
• 新增临时表temp存储包含全局统计值的结果集，主查询直接引用预计算的值即可。

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