mysql - 为什么整数转换不适用于整数 group_concat() 列表？

Question

我被困在需要连接表 ID 的查询中。从那组 ID 中，我需要在子查询中获取那些行。但是当我尝试这样做时，MySQL 将 group_concat() 视为一个字符串。所以那个条件变成假的。

select count(*) 
from rides r 
where r.ride_status = 'cancelled' 
      and r.id IN (group_concat(rides.id))

*************** 下面的原始查询 **************

-- Daily Earnings for 7 days [Final]

select 
          group_concat(rides.id) as ids, 
          group_concat(ride_category.name) as rideType, 
          group_concat(ride_cars.amount + ride_cars.commission) as rideAmount ,
          group_concat(ride_types.name) as carType,
          count(*) as numberOfRides,
          (
            select count(*) from rides r where r.ride_status = 'cancelled' and r.id IN  (group_concat(rides.id) )
          ) as cancelledRides,
          (
            select count(*) from rides r where r.`ride_status` = 'completed' and r.id IN (group_concat(rides.id))
          ) as completedRides,
          group_concat(ride_cars.status) as status,
          sum(ride_cars.commission) + sum(ride_cars.amount) as amount, 
          date_format(from_unixtime(rides.requested_at/1000 + rides.offset*60), '%Y-%m-%d') as requestedDate, 
          date_format(from_unixtime(rides.requested_at/1000 + rides.offset*60), '%V') as week
from 
          ride_cars,
          rides,
          ride_category,
          ride_type_cars,
          ride_types
where 
          ride_cars.user_id = 166
          AND (rides.ride_status = 'completed' or.  rides.ride_status = 'cancelled')
          AND ride_cars.ride_id = rides.id 
          AND (rides.requested_at >= 1559347200000 AND requested_at < 1561852800000)
          AND rides.ride_category = ride_category.id 
          AND ride_cars.car_model_id = ride_type_cars.car_model_id
          AND ride_cars.ride_type_id = ride_types.id
group by
          requestedDate;

任何解决方案将不胜感激。

Answer 1

尝试替换子查询

(select count(*) from rides r where r.ride_status = 'cancelled' and r.id IN  (group_concat(rides.id) )) as cancelledRides,

下面使用 SUM 和 CASE 进行计数，它将使用 GROUP BY

SUM(CASE WHEN rides.ride_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledRides

completedRides 也是如此

并转向使用 JOIN 而不是隐式连接