mysql - 使用count(column)as x.count(column)mysql

这是我的代码，我希望有人能帮我解决问题。我的问题是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函数P.计数。检查参考手册中的“函数名解析和解析”部分

最佳答案

在黑暗中刺杀，但至少基于返回到相同表名的别名

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