为什么在WHERE子句中使用硬编码值时，Group BY语句中出现错误

Question

我是PostgreSQL的新手。我正在处理一个出租车预约申请。

我正试图找出(1(不起作用的原因。硬编码值将由用户输入替换。

1. 非工作：

   选择MAX(cr.pickup_point(作为拾取点、MAX(cr.destination(作为目的地，MAX(cr.leave_time(AS leave_time、MAX(cr.license(AS许可证，MAX(cr.username(AS用户名、MAX(cr.car_ride_id(AS car_，最大(isd.no_of_seats(AS no_of-seats自车侧cr，is_driver isd其中cr.username=isd.usernameAND cr.license=isd.license ANDLOWER(TRIM(拾取点((：：varchar=LOWER(修剪("帕亚-勒巴尔东部，帕亚-勒巴尔"(：varchar以及u2028(LOWER(微调(cr.destination((：：varchar=LOWER(TRIM('Kranji，Sungei-Kadut'((：varcharAND(cr.leave_time(>now((：：时间戳(0(分组依据(cr.car_ride_id，isd.no_of_seats(HAVING isd.no_of_seats>ALL(从出价b中选择计数(*(，car_ride cr2 WHEREb.driver_username=cr2.username AND b.successful IS TRUEAND b.leave_time>now((：：timestamp(0(AND cr2.leave_time=b.leave_timeAND 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(

Answer 1

在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)

当您以可读性更强的样式格式化查询时，更容易避免这些错误。