php - 为什么在 WHERE 子句中使用硬编码值时在 Group BY 语句中出现错误?
问题描述
我是 PostgreSQL 新手。我正在开发出租车预订应用程序。
我试图找出为什么(1)不起作用。硬编码值将被用户输入替换。
非工作:
SELECT MAX(cr.pickup_point) AS Pickup_point, MAX(cr.destination) AS destination, MAX(cr.leave_time) AS leave_time,MAX(cr.license) AS license, MAX(cr.username) AS username, MAX(cr. car_ride_id) AS car_ride_id , MAX(isd.no_of_seats) AS no_of_seats FROM car_ride cr , is_driver isd WHERE cr.username = isd.username AND cr.license = isd.license AND LOWER(TRIM(pickup_point))::varchar = LOWER(TRIM ('东巴耶利峇'))::varchar AND (LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar AND (cr.leave_time) > now()::timestamp(0) GROUP BY(cr.car_ride_id, isd.no_of_seats) HAVING isd.no_of_seats> ALL (SELECT COUNT(*) FROM bids b, car_ride cr2 WHERE b.driver_username = cr2.username AND b.成功为真 AND b.leave_time >now()::timestamp(0) AND cr2.leave_time = b.leave_time AND cr。car_ride_id = cr2.car_ride_id)
2.工作:
SELECT MAX(cr.pickup_point) AS pickup_point , MAX(cr.destination)
AS destination , MAX(cr.leave_time) AS leave_time ,
MAX(cr.license) AS license , MAX(cr.username)
AS username, MAX(cr.car_ride_id) AS car_ride_id , MAX(isd.no_of_seats) AS no_of_seats
FROM car_ride cr , is_driver isd WHERE cr.username = isd.username
AND cr.license = isd.license AND
(cr.leave_time)> now()::timestamp(0) GROUP BY(cr.car_ride_id, isd.no_of_seats) HAVING
isd.no_of_seats>
ALL (SELECT COUNT(*) FROM bids b , car_ride cr2 WHERE
b.driver_username = cr2.username AND b.successful IS TRUE
AND b.leave_time >now()::timestamp(0) AND cr2.leave_time = b.leave_time
AND cr.car_ride_id = cr2.car_ride_id )
(1.) 和 (2.) 之间的区别在于前者打算根据接送点和目的地过滤结果。
而 (2) 显示所有未来且未完全预订的乘车广告(对于特定的 car_ride_id < car_capacity 的出价数 = TRUE),以便汽车未完全预订。
请注意,我已多次使用 MAX 聚合,因为我已将它们用作后续表单中的隐藏字段以进行投标。
错误:“GROUP”处或附近的语法错误
第 10 行:分组依据(cr.car_ride_id,isd.no_of_seats)
解决方案
AND
您在and之间有一个没有关闭的不必要的括号LOWER
:
AND (LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar
固定的:
SELECT MAX(cr.pickup_point) AS pickup_point , MAX(cr.destination) AS destination,
MAX(cr.leave_time) AS leave_time,MAX(cr.license) AS license ,
MAX(cr.username) AS username, MAX(cr.car_ride_id) AS car_ride_id ,
MAX(isd.no_of_seats) AS no_of_seats
FROM car_ride cr , is_driver isd
WHERE cr.username = isd.username
AND cr.license = isd.license
AND LOWER(TRIM(pickup_point))::varchar = LOWER(TRIM('Paya Lebar East, Paya Lebar'))::varchar
AND /*(*/LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar
AND (cr.leave_time)> now()::timestamp(0)
GROUP BY(cr.car_ride_id, isd.no_of_seats)
HAVING isd.no_of_seats> ALL (SELECT COUNT(*)
FROM bids b , car_ride cr2
WHERE b.driver_username = cr2.username
AND b.successful IS TRUE
AND b.leave_time >now()::timestamp(0)
AND cr2.leave_time = b.leave_time
AND cr.car_ride_id = cr2.car_ride_id)
当您以更易读的方式格式化查询时,更容易避免这些错误。