MySQL Join 使用 WHERE 子句返回太多行

Question

我在执行 SQL 查询时遇到问题。所以在我的项目中用户可以预订乘车。我想按用户 ID (passenger_id) 显示预订的行程，但当用户仅为其中一个司机广告预订行程时，查询会返回所有用户 (driver_id) 广告。

    SELECT advertisement.id
         , COUNT(review.driver_id) AS 'review_count'
         , ROUND(AVG(review.mark) ,1) AS 'rating'
         , users.unique_id
         , users.name
         , users.surname
         , users.phone
         , YEAR(CURDATE()) - YEAR(users.birthdate) AS age
         , users.image
         , advertisement.from_city
         , advertisement.to_city
         , users.car_name
         , users.car_model
         , users.car_make_year
         , advertisement.number_of_places
         , advertisement.price
         , advertisement.datetime
         , advertisement.info
      FROM reserved_rides
      JOIN advertisement  
        ON reserved_rides.driver_id = advertisement.user_id
      LEFT 
      JOIN review 
        ON reserved_rides.driver_id = review.driver_id
      JOIN users 
        ON reserved_rides.driver_id = users.unique_id
     WHERE reserved_rides.passenger_id = ?
     GROUP 
        BY advertisement.id
     ORDER 
        BY advertisement.datetime ASC

这里出了什么问题？

Answer 1

我希望将 GROUP BYadvertisement.id 替换为 GROUP BYserved_rides.driver_id 可以解决您的问题。干杯