时间戳

首页 > 解决方案 > 基于其他选择的mysql选择

mysql - 基于其他选择的mysql选择

问题描述

亲爱的，我有以下查询，它会根据案例获得计数，并且工作正常

select users.firstName,users.lastName,users.id,users.phoneNumber,
count(CASE
        WHEN orders.`orderStatus` = 4 THEN 1 ELSE null END) As completed,
        count(CASE
        WHEN orders.`orderStatus`  = 5 THEN 1 ELSE null END) as CustomerCancelled,
        count(CASE
        WHEN orders.`orderStatus`  = 11 THEN 1 ELSE null END) as providerCancelled,
        count(`orders`.`createdAt`) as total,
        from users,providers,orders
        where
        `orders`.`providerId` = `providers`.`id`
        and
        users.id = `providers`.userId
        and
        `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
        GROUP BY users.id;

我需要添加的是获取 CustomerCancelled/total 的计数并将其与每条记录一起显示

我试图像下面那样划分它但没有工作

select users.firstName,users.lastName,users.id,users.phoneNumber,
count(CASE
        WHEN orders.`orderStatus` = 4 THEN 1 ELSE null END) As completed,
        count(CASE
        WHEN orders.`orderStatus`  = 5 THEN 1 ELSE null END) as CustomerCancelled,
        count(CASE
        WHEN orders.`orderStatus`  = 11 THEN 1 ELSE null END) as providerCancelled,
        count(`orders`.`createdAt`) as total,
        CustomerCancelled/total //// this is what i tried to do
        from users,providers,orders
        where
        `orders`.`providerId` = `providers`.`id`
        and
        users.id = `providers`.userId
        and
        `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
        GROUP BY users.id;

标签： mysqlcase

解决方案

您不能SELECT在查询部分使用别名。您需要明确写出表达式：

select users.firstName,
       users.lastName,
       users.id,
       users.phoneNumber,
       COUNT(CASE WHEN orders.`orderStatus` = 4 THEN 1 END) aS completed,
       COUNT(CASE WHEN orders.`orderStatus` = 5 THEN 1 END) AS CustomerCancelled,
       COUNT(CASE WHEN orders.`orderStatus`  = 11 THEN 1 END) AS providerCancelled,
       COUNT(`orders`.`createdAt`) AS total,
       COUNT(CASE WHEN orders.`orderStatus`  = 5 THEN 1 END) / COUNT(`orders`.`createdAt`) AS ratio_cancelled
FROM users
JOIN providers
JOIN orders
WHERE `orders`.`providerId` = `providers`.`id`
  AND users.id = `providers`.userId
  AND `orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
GROUP BY users.id;

请注意，您的表达式中不需要 a ELSE null，CASE因为这是默认设置。此外，您应该编写显式JOIN语句而不是使用已弃用的逗号样式 implicit JOIN。

推荐阅读