如何将GROUP BY应用于MySQL中的多个SELECT语句

Question

I正在创建从多个表中选择数据的多路径选择查询。我已经完成了所有的查询，但现在我必须将GROUP BY应用于SECOND select查询，但得到的错误是Subquery返回超过1行。

GROUP BY在不处理suquery中获取错误为Subquery返回的行超过1行。

我正在使用以下查询：

SELECT 
( SELECT COUNT(bookings.user_id) as cleanings
FROM users INNER JOIN bookings
ON bookings.user_id = users.id 
WHERE users.is_provider = 1 
AND bookings.booking_status_id = 4 GROUP BY user.id) AS cleanings,
users.first_name,
users.last_name,
providerservicemaps.amount,
TRUNCATE(SUM(userreviews.rating)*5/(COUNT(userreviews.user_review_by)*5), 
1) AS rating,
users.profilepic,
postcodes.postcode,
postcodes.suburb,
servicecategories.name,
provider_working_hours.start_time,
provider_working_hours.end_time
FROM users 
INNER JOIN provider_working_hours
ON provider_working_hours.provider_id = users.id
INNER JOIN provider_postcode_maps
ON provider_postcode_maps.provider_id = users.id
INNER JOIN postcodes
ON postcodes.id = provider_postcode_maps.postcode_id
INNER JOIN providerservicemaps
ON providerservicemaps.provider_user_id = users.id
INNER JOIN userreviews
ON userreviews.user_review_for = users.id  
INNER JOIN services
ON providerservicemaps.service_id = services.id
INNER JOIN servicecategories
ON services.category_id = servicecategories.id
INNER JOIN bookings
ON bookings.user_id = users.id
INNER JOIN bookingstatuses
ON bookings.booking_status_id = bookingstatuses.id  
where users.is_provider=1 
AND
postcodes.postcode LIKE '$postcode' AND postcodes.suburb LIKE 
'$suburb'
AND servicecategories.name LIKE '$catagories'
AND FIND_IN_SET('$working_day',provider_working_hours.working_days)
AND provider_working_hours.start_time>='$start_time_result'AND provider_working_hours.end_time>='$end_time_result'
AND bookings.booking_time>='$start_time_result'
AND bookings.number_of_hours>='$work_hours'
GROUP BY users.first_name,
users.last_name,
providerservicemaps.amount,
userreviews.user_review_for,
users.profilepic,
postcodes.postcode,
postcodes.suburb,
servicecategories.name,
provider_working_hours.start_time,
provider_working_hours.end_time;

Answer 1

这是您的错误来源：

( SELECT COUNT(bookings.user_id) as cleanings
FROM users INNER JOIN bookings
ON bookings.user_id = users.id 
WHERE users.is_provider = 1 
AND bookings.booking_status_id = 4 GROUP BY user.id) AS cleanings

如果您想将someting作为列返回，它需要是一个原子值。因此不能返回多行。

在没有任何上下文的情况下，我可以通过使用CTE为您提供一种消除错误的方法。您必须决定是否保留该逻辑。

WITH CTE AS 
( SELECT users.user_id, COUNT(bookings.user_id) as cleanings
FROM users INNER JOIN bookings
ON bookings.user_id = users.id 
WHERE users.is_provider = 1 
AND bookings.booking_status_id = 4 
GROUP BY user.id
)
SELECT 
CTE.cleanings,
users.first_name,
users.last_name,
providerservicemaps.amount,
TRUNCATE(SUM(userreviews.rating)*5/(COUNT(userreviews.user_review_by)*5), 
1) AS rating,
users.profilepic,
postcodes.postcode,
postcodes.suburb,
servicecategories.name,
provider_working_hours.start_time,
provider_working_hours.end_time
FROM users 
INNER JOIN CTE 
ON CTE.user_id=users.user_id
INNER JOIN provider_working_hours
ON provider_working_hours.provider_id = users.id
INNER JOIN provider_postcode_maps
ON provider_postcode_maps.provider_id = users.id
INNER JOIN postcodes
ON postcodes.id = provider_postcode_maps.postcode_id
INNER JOIN providerservicemaps
ON providerservicemaps.provider_user_id = users.id
INNER JOIN userreviews
ON userreviews.user_review_for = users.id  
INNER JOIN services
ON providerservicemaps.service_id = services.id
INNER JOIN servicecategories
ON services.category_id = servicecategories.id
INNER JOIN bookings
ON bookings.user_id = users.id
INNER JOIN bookingstatuses
ON bookings.booking_status_id = bookingstatuses.id  
where users.is_provider=1 
AND
postcodes.postcode LIKE '$postcode' AND postcodes.suburb LIKE 
'$suburb'
AND servicecategories.name LIKE '$catagories'
AND FIND_IN_SET('$working_day',provider_working_hours.working_days)
AND provider_working_hours.start_time>='$start_time_result'AND provider_working_hours.end_time>='$end_time_result'
AND bookings.booking_time>='$start_time_result'
AND bookings.number_of_hours>='$work_hours'
GROUP BY users.first_name,
users.last_name,
providerservicemaps.amount,
userreviews.user_review_for,
users.profilepic,
postcodes.postcode,
postcodes.suburb,
servicecategories.name,
provider_working_hours.start_time,
provider_working_hours.end_time;

另一种选择是对cleanings列使用相关的子查询，如下所示：

( SELECT COUNT(bookings.user_id) as cleanings
FROM bookings
WHERE user_id = users.id 
WHERE booking_status_id = 4 ) AS cleanings