PostgreSQL INNER JOIN/FROM 错误

Question

我是 SQL 的新手，我想弄清楚为什么会出现错误。我正在尝试向查询中添加 2 列。

原代码如下:

select f.id ride_id,f.driver_id,f.fleetid,f.fare,f.tip,f.service_fee,(f.fare+f.tip+f.service_fee) total,f.pick_up_city,f.pick_up_state,f.created_at
from
(select r.id,ra.driver_id,r.created_at,r.pick_up_city,r.pick_up_state,ra.xid fleetid,
MAX(CASE WHEN cli.category='fare' THEN cli.amount ELSE NULL END)/100 fare,
MAX(CASE WHEN cli.category='gratuity' THEN cli.amount ELSE NULL END)/100 tip,
MAX(CASE WHEN cli.category='flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
from rides r,charges c,charge_line_items cli,(select distinct ri.id,h.driver_id,f.xid
from rides ri inner join hails h on ri.id=h.ride_id
 inner join vehicles v on h.vehicle_id=v.id
  inner join fleets f on v.fleet_id=f.id
 where ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
and h.status='completed' and ri.created_at between '2015-07-15 04:00:00' and '2015-09-11 04:00:00' 
 and f.xid in ('10202')) ra
where r.id=ra.id and r.id=c.ride_id and c.id=cli.charge_id
group by r.id,ra.xid,ra.driver_id) f

我正在尝试查找驱动程序的名字和姓氏。我在 h.driver_id::VARCHAR = dr.xid 和 SELECT 子句中的 dr.first_name、dr.last_name 上添加 INNER JOIN 驱动程序 AS dr。 (我在我添加的部分周围加了星号。)

select f.id ride_id,f.driver_id,**dr.first_name,dr.last_name,**f.fleetid,f.fare,f.tip,f.service_fee,(f.fare+f.tip+f.service_fee) total,f.pick_up_city,f.pick_up_state,f.created_at
from
(select r.id,ra.driver_id,r.created_at,r.pick_up_city,r.pick_up_state,ra.xid fleetid,
MAX(CASE WHEN cli.category='fare' THEN cli.amount ELSE NULL END)/100 fare,
MAX(CASE WHEN cli.category='gratuity' THEN cli.amount ELSE NULL END)/100 tip,
MAX(CASE WHEN cli.category='flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
from rides r,charges c,charge_line_items cli,(select distinct ri.id,h.driver_id,f.xid
from rides ri inner join hails h on ri.id=h.ride_id
 inner join vehicles v on h.vehicle_id=v.id
  inner join fleets f on v.fleet_id=f.id
     **inner join drivers AS dr on h.driver_id::VARCHAR = dr.xid**
 where ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
and h.status='completed' and ri.created_at between '2015-07-15 04:00:00' and '2015-09-11 04:00:00' 
 and f.xid in ('10202')) ra
where r.id=ra.id and r.id=c.ride_id and c.id=cli.charge_id
group by r.id,ra.xid,ra.driver_id) f

当我运行这个查询时，我收到一条错误消息“缺少表 dr 的 FROM 子句条目”。通常，我可以只加入我想从中选择的表，但由于某种原因，查询中有些东西不允许我这样做。

如有任何帮助，我们将不胜感激。我仍在学习，真的很想弄清楚我做错了什么。

谢谢!

Answer 1

您已在二级子查询中添加了 JOIN。这很好，但是您必须使用分配给子查询的别名将二级子查询的选择列表中的两列“传播”到主级别。正确概述代码会使错误和解决方案显而易见:

SELECT f.id <b>AS</b> ride_id, f.driver_id,
       <b>f.first_name, f.last_name,</b>
       f.fleetid, f.fare, f.tip, f.service_fee, (f.fare+f.tip+f.service_fee) total,
       f.pick_up_city, f.pick_up_state, f.created_at
FROM (
  <b>-- First sub-query starting</b>
  SELECT r.id, ra.driver_id, r.created_at, r.pick_up_city, r.pick_up_state, ra.xid <b>AS</b> fleetid,
         <b>ra.first_name, ra.last_name,</b>
         max(CASE WHEN cli.category = 'fare' THEN cli.amount ELSE NULL END)/100 fare,
         max(CASE WHEN cli.category = 'gratuity' THEN cli.amount ELSE NULL END)/100 tip,
         max(CASE WHEN cli.category= 'flywheel_service_fee' THEN cli.amount ELSE NULL END)/100 service_fee
  FROM rides r, charges c, charge_line_items cli, (
    <b>-- Second sub-query starting</b>
    SELECT DISTINCT ri.id, h.driver_id, f.xid, <b>dr.first_name, dr.last_name</b>
    FROM rides ri
    INNER JOIN hails h ON ri.id = h.ride_id
    INNER JOIN vehicles v ON h.vehicle_id = v.id
    INNER JOIN fleets f ON v.fleet_id = f.id
    <b>INNER JOIN drivers dr ON h.driver_id::varchar = dr.xid</b>
    WHERE ri.status IN ('completed', 'waiting_for_payment_data', 'waiting_for_payment_instrument', 'processing_payment', 'payment_pending') 
      AND h.status = 'completed'
      AND ri.created_at BETWEEN '2015-07-15 04:00:00' AND '2015-09-11 04:00:00' 
      AND f.xid IN ('10202')) ra
    <b>-- End of second sub-query</b>
  WHERE r.id = ra.id
    AND r.id = c.ride_id
    AND c.id = cli.charge_id
  -- The below GROUP BY clause is incomplete, see added line
  GROUP BY r.id, ra.xid, ra.driver_id
           <b>, r.created_at, r.pick_up_city, r.pick_up_state, ra.first_name, ra.last_name</b>) f
  <b>-- End of first sub-query</b>
;

否则，原始查询是一种奇怪的编码风格组合，例如 - 用于列出多个表的两种风格(逗号分隔和 JOIN 子句)，奇数大写和 - 正如你刚刚学到了-蹩脚的大纲。看来你不是唯一一个有一些东西要学的人......