mysql - 我的 SQL 查询 : Error :Unknown column in field list when using CASE WHEN in WHERE clause

Question

大家好，我是mysql查询的初学者。我在 mysql 中使用此查询时遇到一个错误:

SELECT enquiry.business_id ,enquiry_count ,b.`business_name`,c.`id`
FROM `consumer_guide_enquiry_company` enquiry
INNER JOIN `businesses` b ON  `enquiry`.`business_id`=b.`business_id`
INNER JOIN `consumer_guide_company` c ON c.`business_id`=b.`business_id` 
WHERE CASE 
WHEN enquiry.`business_id` 
IN (
SELECT b.business_id 
FROM `businesses` b,`consumer_guide_company` c 
WHERE b.`business_id`=c.`business_id` AND c.`consumer_guide_id`=1) 
THEN enquiry_count=COUNT(enquiry.enquiry_id) 
ELSE enquiry_count=0 
END
GROUP BY enquiry.business_id

显示错误:“字段列表”中未知列“enquiry_count”

感谢您的帮助

Answer 1

尝试这样

SELECT enquiry.business_id ,case when enquiry_count is NULL then  0 
else enquiry_count end 'enquiry_count'  ,b.`business_name`,c.`id`
FROM `consumer_guide_enquiry_company` enquiry

INNER JOIN `businesses` b ON  `enquiry`.`business_id`=b.`business_id`
INNER JOIN `consumer_guide_company` c ON c.`business_id`=b.`business_id` 

LEFT JOIN (SELECT b.business_id,count(*) as enquiry_count FROM `businesses`
b,`consumer_guide_company` c WHERE b.`business_id`=c.`business_id` AND  
c.`consumer_guide_id`=1 group by b.business_id) as tt 
ON enquiry.`business_id`=tt.b.business_id
GROUP BY enquiry.business_id