含子查询的SQL报错：无效列名'VisitCode'（列实际存在）

你遇到的问题核心是PIVOT操作会移除作为行转列依据的原列：你的SQL里用了FOR VisitCode IN ([1], [2])，执行PIVOT后，PVTTABLE里就不再有VisitCode列了——这个列的取值已经被转换成了[1]和[2]这两个新列，所以外层查询直接引用VisitCode自然会报错。

而且看你的需求是统计不同VisitCode的次数，原SQL里的PIVOT其实是多余的（你外层根本没用到PIVOT生成的[1]/[2]列），完全可以去掉PIVOT简化逻辑，同时解决报错。

修正后的SQL（无PIVOT版本） #

SELECT 
  b.SiteID, 
  c.AreaName, 
  SUM(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PM],
  COUNT(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PMCount],
  SUM(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CM],
  COUNT(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CMCount],      
  SUM(CASE WHEN sales_data.VisitCode IN (1,2) THEN 1 END) AS [Total]
FROM (
  SELECT 
    a.SiteCode, 
    a.VisitCode,
    a.AreaCode,
    a.RequiredFuelFilled,
    a.RequiredVisitDate
  FROM tblFuelTroubleTickets a
  WHERE a.RequiredVisitDate BETWEEN '2023-03-01 00:00:00.000' AND '2023-03-15 23:59:59.999' 
) sales_data
INNER JOIN tblSites b
ON sales_data.SiteCode = b.SiteCode
INNER JOIN tblAreas c
ON sales_data.AreaCode = c.ID
GROUP BY b.SiteID, c.AreaName, b.SiteCode
ORDER BY [Total] DESC

修改说明 #

• 移除了多余的PIVOT操作，直接基于sales_data子查询关联表进行统计
• 明确指定sales_data.VisitCode（避免列名歧义）
• 简化了Total的计算逻辑，可读性更强

如果确实需要保留PIVOT（比如要用到RequiredFuelFilled的分组汇总） #

可以通过保留原VisitCode列的方式实现，示例如下：

SELECT 
  b.SiteID, 
  c.AreaName, 
  SUM(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PM],
  COUNT(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PMCount],
  SUM(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CM],
  COUNT(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CMCount],      
  SUM(CASE WHEN sales_data.VisitCode IN (1,2) THEN 1 END) AS [Total],
  PVTTABLE.[1] AS PM_FuelFilled, -- 调用PIVOT生成的加油量汇总列
  PVTTABLE.[2] AS CM_FuelFilled
FROM (
  SELECT 
    a.SiteCode, 
    a.VisitCode,
    a.AreaCode,
    a.RequiredFuelFilled,
    a.RequiredVisitDate
  FROM tblFuelTroubleTickets a
  WHERE a.RequiredVisitDate BETWEEN '2023-03-01 00:00:00.000' AND '2023-03-15 23:59:59.999' 
) sales_data
PIVOT (
  SUM(RequiredFuelFilled)
  FOR VisitCode IN ([1], [2])
) AS PVTTABLE
INNER JOIN tblSites b
ON PVTTABLE.SiteCode = b.SiteCode
INNER JOIN tblAreas c
ON PVTTABLE.AreaCode = c.ID
GROUP BY b.SiteID, c.AreaName, b.SiteCode, PVTTABLE.[1], PVTTABLE.[2]
ORDER BY [Total] DESC

这里统计逻辑基于原sales_data的VisitCode列，同时保留了PIVOT生成的加油量汇总结果，需注意将PIVOT生成的列加入GROUP BY中。

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