时间戳

首页 > 解决方案 > 尝试子查询时出现不明确的列名错误

sql - 尝试子查询时出现不明确的列名错误

问题描述

提示是假设风险“1”设施每年必须进行至少 3 次类型的检查。显示 2013 年未能满足此要求且至少有 1 次 Canvass 类型检查失败的设施。显示设施名称以及 2013 年有多少 Canvass 检查。提示：使用子查询

所以这是我的代码

SELECT DBAName, COUNT(*)
FROM FoodInspectionOriginal, Organization
WHERE Risk = 1 AND [Inspection Type] IN (SELECT [Inspection Type] FROM FoodInspectionOriginal WHERE [Inspection Type] = 'Canvass' AND [Inspection Date] = Year(2013))
GROUP BY DBAName;

我得到这是输出

消息 209，第 16 层，状态 1，第 3 行

不明确的列名称“风险”。

标签： sqlsql-serversubqueryaggregate-functionshaving-clause

解决方案

我只会使用聚合。假设 table 的表结构如下foodinspectionoriginal：

dbaname           -- name of the facility
inspection_date   
inspection_type   -- "Canvass", ...
inspection_status -- "successful", "failed"

该查询可以表述如下：

select dbaname,
    sum(case when inspection_type = 'Canvass' and inspection_status = 'failed' then 1 else 0 end) as cnt_failed_canvass
from foodinspectionoriginal
where 
    risk = 1 
    and inspection_date >= '20130101' 
    and inspection_date <  '20140101'
group by dbaname 
having 
    count(*) < 3 
    and sum(case when inspection_type = 'Canvass' and inspection_status = 'failed' then 1 else 0 end) > 0

推荐阅读