mysql left join with count返回未知列

oknwwptz 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(320)

亲爱的我有下面的查询，以计算有多少垃圾邮件为每个用户和总订单
我做了左边的加入，因为不是所有的订单都有垃圾邮件

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得到以下错误
“on子句”中的未知列“orders.id”
这里有什么问题？我做了左连接正确的基础上，旧的查询工作良好

mysql Database jquery

来源：https://stackoverflow.com/questions/53403468/mysql-left-join-with-count-returns-unknown-column

1条答案

按热度按时间

vfh0ocws1#

我修复了这个问题，我认为这是语法问题，下面是一个新的查询

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;

赞(0）回复(0）举报 2021-06-19

我来回答

mysql left join with count返回未知列

1条答案

相关问题

热门标签

最新问答