英文:
Oracle SQL Developer - Adding multiple subqueries to find latest date values
问题
我试图从四个不同的表中选择数据：订单、约会、计划和绩效卡。对于约会、计划和绩效卡表，我试图提取提货和送货日期。所有订单都有计划和绩效卡日期，但不是所有都有约会日期。约会和计划表分别有一行用于提货和一行用于送货，绩效卡表在单个行中同时包含两者。绩效卡表只有一个提货和一个送货日期，对于每个订单来说都不会更改，但对于约会和计划表，日期可能会由于重新安排、订单更新等原因而发生更改。每当约会或计划日期被修改时，都会在表中添加新条目。我正在寻找最新的修改后的约会和计划日期。不一定是最大日期，因为有时约会和计划日期可能会提前重新安排。
这个查询只是针对单个订单ID的（最终我想选择特定日期范围内的若干订单），并且返回了三行，因为约会有两次额外的修改。将查询保留在这种状态将反映所有的约会和计划日期修改。我只想要最新的约会和计划日期的一行。
select o.order_primary_reference, o.order_id,
pu.appointment_date as latest_pickup_appt,
dl.appointment_date as latest_del_appt,
pick.plan_date as latest_pickup_plandate, del.plan_date as latest_del_plandate,
sc.scorecard_exp_pickup_date as scorecard_expected_pickup, sc.scorecard_exp_delivery_date as scorecard_expected_del
from orders o
join plan pick
on o.order_id = pick.plan_order_id and pick.stop_type = 'P'
join plan del
on o.order_id = del.plan_order_id and del.stop_type = 'D'
join scorecard sc
on o.order_id = sc.scorecard_order_id
left outer join appointment pu
on o.order_id = pu.appointment_order_id and pu.appointment_type = 'P'
left outer join appointment dl
on o.order_id = dl.appointment_order_id and dl.appointment_type = 'D'
where o.order_id = 123456
这个查询将获取我在这个实例中想要的日期（来自约会表中最近添加的提货约会），但当我尝试将其作为子查询合并到我的更大查询中时，出现了“too many values”错误。如何将选择最近添加的约会和计划日期合并到我的查询中？
select *
from
(select pu.appointment_date,
row_number() over (partition by pu.appointment_order_id
order by pu.appointment_date desc) rn
from appointment pu
where pu.appointment_type = 'P'
and pu.appointment_order_id = 123456
)
where rn = 1
希望这能够满足您的需求。
英文:
I am trying to select data from four different tables: orders, appointment, plan, and scorecard. For the appointment, plan, and scorecard tables I am trying to extract pickup and delivery dates. All orders have plan and scorecard dates but not all have appointment dates. Appointment and plan tables have one row for pickup and one row for delivery, scorecard table has both in a single row. The scorecard table just has one pickup and one delivery date for each order that never changes, but for the appointment and plan tables, the dates can be revised for a single order due to reschedules, order updates, etc. Each time an appointment or plan date is revised, a new entry is added to the table. I am looking for the latest revised appointment and plan dates. Not necessarily the max date, because sometimes the appointments and plan dates are rescheduled to earlier dates.
This query is just for a single order id (eventually I would like to select a number of orders for a specific date range), and it returns three rows because there were two additional revisions to the appointment. Leaving the query like this would reflect all revisions of appointment and plan dates. I want just the one row with the latest appointment and plan dates.
select o.order_primary_reference, o.order_id,
pu.appointment_date as latest_pickup_appt,
dl.appointment_date as latest_del_appt,
pick.plan_date as latest_pickup_plandate, del.plan_date as latest_del_plandate,
sc.scorecard_exp_pickup_date as scorecard_expected_pickup, sc.scorecard_exp_delivery_date as scorecard_expected_del
from orders o
join plan pick
on o.order_id = pick.plan_order_id and pick.stop_type = 'P'
join plan del
on o.order_id = del.plan_order_id and del.stop_type = 'D'
join scorecard sc
on o.order_id = sc.scorecard_order_id
left outer join appointment pu
on o.order_id = pu.appointment_order_id and pu.appointment_type = 'P'
left outer join appointment dl
on o.order_id = dl.appointment_order_id and dl.appointment_type = 'D'
where o.order_id = 123456
This query will get the date that I want in this instance (the most recently added pickup appointment from the appointment table), but when I tried incorporating this into my larger query as a subquery I was getting a too many values error. How can I incorporate selecting the most recently added appointment and plan dates into my query?
select *
from
(select pu.appointment_date,
row_number() over (partition by pu.appointment_order_id
order by pu.appointment_date dsc) rn
from appointment pu
where pu.appointment_type = 'P'
and pu.appointment_order_id = 123456
)
where rn = 1
答案1
得分: 1
以下是翻译好的部分：
With some sample data like this:
与以下示例数据一样：
Main SQL
主要SQL
... if I got it right, you should Inner Join scores to orders and Left Join plans and appoints and then fetch the MAX() dates from last two...
如果我理解正确，您应该将scores与orders进行内连接，将plans和appoints进行左连接，然后获取最后两个日期的MAX()值...
... with the sample date as above - this will result as:
... 使用上述示例日期 - 结果如下：
英文:
With some sample data like this:
WITH
orders AS
(
Select 123 "ORDER_ID", 'ORDPRIMREF 123' "ORDER_PRIM_REF" From Dual Union All
Select 456 "ORDER_ID", 'ORDPRIMREF 456' "ORDER_PRIM_REF" From Dual Union All
Select 789 "ORDER_ID", 'ORDPRIMREF 789' "ORDER_PRIM_REF" From Dual
),
appoints AS
(
Select 123 "APP_ORDER_ID", To_Date('23.02.2023', 'dd.mm.yyyy') "APP_DATE", 'P' "APP_TYPE" From Dual Union All
Select 123 "APP_ORDER_ID", To_Date('27.02.2023', 'dd.mm.yyyy') "APP_DATE", 'D' "APP_TYPE" From Dual Union All
Select 456 "APP_ORDER_ID", To_Date('26.02.2023', 'dd.mm.yyyy') "APP_DATE", 'P' "APP_TYPE" From Dual Union All
Select 456 "APP_ORDER_ID", To_Date('27.02.2023', 'dd.mm.yyyy') "APP_DATE", 'P' "APP_TYPE" From Dual Union All
Select 456 "APP_ORDER_ID", To_Date('02.03.2023', 'dd.mm.yyyy') "APP_DATE", 'D' "APP_TYPE" From Dual Union All
Select 456 "APP_ORDER_ID", To_Date('03.03.2023', 'dd.mm.yyyy') "APP_DATE", 'D' "APP_TYPE" From Dual Union All
Select 789 "APP_ORDER_ID", Null "APP_DATE", 'P' "APP_TYPE" From Dual
),
plans AS
(
Select 123 "PL_ORDER_ID", To_Date('28.02.2023', 'dd.mm.yyyy') "PL_DATE", 'P' "PL_STP_TYPE" From Dual Union All
Select 456 "PL_ORDER_ID", To_Date('17.02.2023', 'dd.mm.yyyy') "PL_DATE", 'D' "PL_STP_TYPE" From Dual Union All
Select 456 "PL_ORDER_ID", To_Date('20.02.2023', 'dd.mm.yyyy') "PL_DATE", 'D' "PL_STP_TYPE" From Dual Union All
Select 789 "PL_ORDER_ID", To_Date('03.03.2023', 'dd.mm.yyyy') "PL_DATE", 'P' "PL_STP_TYPE" From Dual
),
scores AS
(
Select 123 "SC_ORDER_ID", To_Date('26.02.2023', 'dd.mm.yyyy') "SC_PICK_DATE", To_Date('01.03.2023', 'dd.mm.yyyy') "SC_DLVR_DATE" From Dual Union All
Select 456 "SC_ORDER_ID", To_Date('27.02.2023', 'dd.mm.yyyy') "SC_PICK_DATE", To_Date('02.03.2023', 'dd.mm.yyyy') "SC_DLVR_DATE" From Dual Union All
Select 789 "SC_ORDER_ID", To_Date('28.02.2023', 'dd.mm.yyyy') "SC_PICK_DATE", To_Date('03.03.2023', 'dd.mm.yyyy') "SC_DLVR_DATE" From Dual
)
... if I got it right, you should Inner Join scores to orders and Left Join plans and appoints and then fetch the MAX() dates from last two...
Main SQL
Select
o.ORDER_PRIM_REF,
o.ORDER_ID,
--
MAX(app_p.APP_DATE) "APP_PICKUP_DATE",
MAX(app_d.APP_DATE) "APP_DELIVERY_DATE",
--
MAX(pl_p.PL_DATE) "PL_PICKUP_DATE",
MAX(pl_d.PL_DATE) "APP_DELIVERY_DATE",
--
sc.SC_PICK_DATE "SCORE_PICKUP_DATE",
sc.SC_DLVR_DATE "SCORE_DELIVERY_DATE"
From
orders o
Inner Join
scores sc ON(o.ORDER_ID = sc.SC_ORDER_ID)
Left join
appoints app_p ON(o.ORDER_ID = app_p.APP_ORDER_ID and app_p.APP_TYPE = 'P')
Left join
appoints app_d ON(o.ORDER_ID = app_d.APP_ORDER_ID and app_d.APP_TYPE = 'D')
Left Join
plans pl_p ON(o.ORDER_ID = pl_p.PL_ORDER_ID and pl_p.PL_STP_TYPE = 'P')
Left Join
plans pl_d ON(o.ORDER_ID = pl_d.PL_ORDER_ID and pl_d.PL_STP_TYPE = 'D')
Group By
o.ORDER_PRIM_REF, o.ORDER_ID,
sc.SC_PICK_DATE, sc.SC_DLVR_DATE
... with the sample date as above - this will result as:
ORDER_PRIM_REF ORDER_ID APP_PICKUP_DATE APP_DELIVERY_DATE PL_PICKUP_DATE APP_DELIVERY_DATE SCORE_PICKUP_DATE SCORE_DELIVERY_DATE
-------------- ---------- --------------- ----------------- -------------- ----------------- ----------------- -------------------
ORDPRIMREF 123 123 23-FEB-23 27-FEB-23 28-FEB-23 26-FEB-23 01-MAR-23
ORDPRIMREF 456 456 27-FEB-23 03-MAR-23 20-FEB-23 27-FEB-23 02-MAR-23
ORDPRIMREF 789 789 03-MAR-23 28-FEB-23 03-MAR-23
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论