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

Question

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

GROUP BY 在不处理 suquery 得到错误是子查询返回超过 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;

标签： mysqlsqlselectgroup-bymulti-select

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

如果您想将某些内容作为列返回，则它需要是原子值。所以你不能返回多行。

没有任何上下文，我可以给你一种通过使用 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;

另一种方法是对清洗列使用相关子查询，如下所示：

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