SQL错误(包含子句中的未知列)。

Question

[SQL]SELECT
vlog.lead_id, vlog.phone_number
FROM
vicidial_log as vlog
GROUP BY vlog.lead_id
HAVING(
CASE 
WHEN vlog.status = 'NA' THEN 1 
WHEN vlog.status = 'ADC' THEN 1 
END
) = COUNT(*)

[Err] 1054 - Unknown column 'vlog.status' in 'having clause'

以上查询不起作用。我正在努力实现这一目标:从意义上说是https://www.db-fiddle.com/f/ej4fM8GptBk9FvGJC8AkUH/0。

 [SQL]SELECT
    vlog.lead_id, vlog.phone_number, vlog.status
    FROM
    vicidial_log as vlog
    GROUP BY vlog.lead_id
    HAVING(
    CASE 
    WHEN vlog.status = 'NA' THEN 1 
    WHEN vlog.status = 'ADC' THEN 1 
    END
    ) = COUNT(*)

这是可行的，但它不能给我我想要的结果

状态=可以是任何东西，lead_id =不唯一，phone_number =不唯一

我正在努力找出至少状态是

ADC
NA
OR BOTH ADC AND NA

任何其他组合都不应返回电话号码。

Answer 1

它是在黑暗中拍摄的，但也许它会对你有用。将HAVING子句更改为：

HAVING SUM(CASE WHEN vlog.status in ('NA','ADC') THEN 1 ELSE 0 END) = COUNT(*)

Answer 2

这似乎是可行的：

SELECT DISTINCT lead_id, phone_number
FROM vicidial_log vlog
WHERE status IN ('NA', 'ADC')
AND NOT EXISTS (SELECT *
                FROM vicidial_log vlog2
                WHERE status NOT IN ('NA','ADC')
                AND vlog.lead_id = vlog2.lead_id
                AND vlog.phone_number = vlog2.phone_number)

不过，还不完全清楚是否需要在lead_id和phone_number上进行比较。