使用count(column)作为x.count(columns)mysql

Question

这是我的代码，我希望任何人都能帮助我解决问题。我的问题在c.count和p.count行。他们每个人都做不同的工作。

SELECT tablesite.name,
tablesite.family,
tablesite.phone_number,
job_list.job_name,
p.COUNT(action.service_provider_id) as positive,
n.COUNT(action.service_provider_id) as negative
FROM tablesite
INNER JOIN relation
on tablesite.id_user=relation.user_id
INNER JOIN job_list
on relation.job_id=job_list.job_id 
LEFT JOIN action p
ON tablesite.id_user=action.service_provider_id
AND action.vote !='' AND action.customer_comment =''
LEFT JOIN action n
ON tablesite.id_user=action.service_provider_id
AND action.vote !='' AND action.customer_comment !=''

GROUP BY name, family,job_name, phone_number

我不能使用p.count或n.count如何解决这个问题。

错误：#1630-FUNCTION p.COUNT不存在。检查参考手册中的"函数名称解析和解析"部分

Answer 1

在黑暗中捅刀子，但基于别名返回相同的表名action至少

SELECT tablesite.name,
tablesite.family,
tablesite.phone_number,
job_list.job_name,
COUNT(p.service_provider_id) as positive,
COUNT(n.service_provider_id) as negative
FROM tablesite
INNER JOIN relation
on tablesite.id_user=relation.user_id
INNER JOIN job_list
on relation.job_id=job_list.job_id 
LEFT JOIN action p
ON tablesite.id_user=p.service_provider_id
AND p.vote !='' AND p.customer_comment =''
LEFT JOIN action n
ON tablesite.id_user=n.service_provider_id
AND n.vote !='' AND n.customer_comment !=''

GROUP BY name, family,job_name, phone_number