jquery - 带有计数的MySQL LEFT JOIN返回未知列

亲爱的我在下面的查询中计算每个用户有多少垃圾邮件以及总订单数

我没有加入，因为并非所有订单都包含垃圾邮件

select users.firstName,users.lastName,users.Id,users.phoneNumber,count(CASE
                            WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as countSpam,
                            count(`orders`.`id`) as totalOrder
                            from `orders`,users,providers
                            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 countSpam DESC;

正在从mysql获取以下错误


  “子句”中的未知列“ orders.id”


这是什么问题？我根据旧查询的工作原理正确完成了LEFT JOIN

最佳答案

我解决了这个问题，我认为这是语法问题，下面正在运行的新查询就像一个超级按钮

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;