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;

最佳答案

您不能在查询的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;

请注意，您不必在ELSE null表达式中使用CASE，因为这是默认设置。另外，您应该编写显式的JOIN语句，而不要使用不建议使用的逗号样式隐式JOIN。

关于mysql - MySQL选择基于其他选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53402547/