我解决了这个问题,我认为这是语法问题,下面是一个像符咒一样工作的新查询
select users.firstName,users.lastName,users.phoneNumber,count(CASE
WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as spamCounter,
ROUND(count(CASE
WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END)/count(orders.id),2) AS ratio_spam,
count(orders.id) as totalOrder
from users,providers,orders
LEFT JOIN comments ON `orders`.`id`= `comments`.`commentableId`
where
orders.`providerId` = providers.id
and
users.id = providers.userId
and
`orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
GROUP BY users.id
ORDER BY spamCounter DESC
LIMIT 20;