我有如下数据。这里我只显示了一些数据，但实际上这些数据是通过连接来自几个表的。
CREATE TABLE project_complete_data (PROJECT_ID, PROJ_COMPANY_NAME, PROJECT_NAME, CUSTOM_LOT_NO, TASK_NAME, TASK_ACTUAL_END_DATE, TASK_FINISH_DATE, TASK_FINISHED, TASK_LAST_MODIFIED_DATE, TASK_LAST_MODIFIED_DATE_TIME, PO_TOTAL_AMOUNT , PO_STATUS, PO_TYPE) AS
SELECT 511, 'A Inc.', 'A', '01a', 'Rough-In Plumbing', DATE '2009-12-17', DATE '2014-09-01', 1, null, null, 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', '02', 'Update Lender Info in BT - Sales Rep', DATE '2005-06-28', DATE '2005-10-11', 1, null, null, 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', '020', 'TK task', DATE '2018-11-07', DATE '2019-06-21', 1, DATE '2019-06-21', TO_DATE('2019-06-21 18:12:21', 'YYYY-MM-DD hh24:mi:ss'), 33, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', '152', 'HVAC rough', DATE '2021-09-16', DATE '2021-08-05', 1, DATE '2021-08-05', TO_DATE('2021-08-05 18:40:05', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'New task 1', DATE '2021-12-10', DATE '2021-12-15', 0, DATE '2021-12-10', TO_DATE('2021-12-10 19:27:47', 'YYYY-MM-DD hh24:mi:ss'), 4, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'New task 2', DATE '2021-12-12', DATE '2021-12-16', 0, DATE '2021-12-12', TO_DATE('2021-12-12 19:27:47', 'YYYY-MM-DD hh24:mi:ss'), 5, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Install Countertops', DATE '2019-04-25', DATE '2021-08-04', 1, DATE '2021-08-04', TO_DATE('2021-08-04 19:27:47', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Inspection: Backfill', DATE '2019-02-08', DATE '2021-08-04', 1, DATE '2021-08-04', TO_DATE('2021-08-04 19:27:47', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Backfill', DATE '2019-02-11', DATE '2019-06-10', 1, DATE '2019-06-21', TO_DATE('2019-06-21 20:47:51', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Set Foundation Walls', DATE '2019-02-01', DATE '2019-02-01', 1, DATE '2019-06-21', TO_DATE('2019-06-21 18:22:23', 'YYYY-MM-DD hh24:mi:ss'), 0, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Dig and Form Footing', DATE '2019-01-24', DATE '2019-01-23', 1, DATE '2019-06-21', TO_DATE('2019-06-21 18:21:29', 'YYYY-MM-DD hh24:mi:ss'), 5, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Pour Footing', DATE '2019-01-29', DATE '2019-01-29', 1, DATE '2019-06-21', TO_DATE('2019-06-21 18:18:26', 'YYYY-MM-DD hh24:mi:ss'), 5, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Excavate Foundation', DATE '2019-01-22', DATE '2019-01-22', 1, DATE '2019-06-19', TO_DATE('2019-06-19 18:15:44', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Bldg C1|Parcel 01&*', 'Stake out Foundation Lot', DATE '2019-01-21', DATE '2019-01-21', 1, DATE '2019-06-19', TO_DATE('2019-06-19 17:25:35', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Finalize Test 2', 'HVAC rough', DATE '2019-01-11', DATE '2021-08-03', 1, DATE '2021-08-03', TO_DATE('2021-08-03 20:32:55', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Finalize Test 2', 'first floor framing', DATE '2018-11-30', DATE '2018-11-26', 1, DATE '2019-06-21', TO_DATE('2019-06-21 23:24:28', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Finalize Test 2', 'second floor framing', DATE '2018-12-07', DATE '2018-10-29', 1, DATE '2019-06-21', TO_DATE('2019-06-21 23:18:38', 'YYYY-MM-DD hh24:mi:ss'), 0, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 4', 'Excavate Foundation', DATE '2018-11-02', DATE '2018-11-05', 1, DATE '2019-06-21', TO_DATE('2019-06-21 18:28:51', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'New task 1', DATE '2021-12-08', DATE '2021-12-11', 0, DATE '2021-12-08', TO_DATE('2021-12-08 20:42:54', 'YYYY-MM-DD hh24:mi:ss'), 6, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'New task 2', DATE '2021-12-09', DATE '2021-12-12', 0, DATE '2021-12-09', TO_DATE('2021-12-09 20:42:56', 'YYYY-MM-DD hh24:mi:ss'), 12, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'electrical rough', DATE '2019-08-15', DATE '2021-08-03', 1, DATE '2021-08-03', TO_DATE('2021-08-03 20:42:54', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'HVAC rough', DATE '2019-08-27', DATE '2021-08-03', 1, DATE '2021-08-03', TO_DATE('2021-08-03 20:42:54', 'YYYY-MM-DD hh24:mi:ss'), 12, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'first floor framing', DATE '2019-08-22', DATE '2021-07-19', 1, DATE '2021-07-19', TO_DATE('2021-07-19 15:52:38', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'first floor framing', DATE '2019-08-22', DATE '2021-07-19', 1, DATE '2021-07-19', TO_DATE('2021-07-19 15:52:38', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Lot # 8', 'second floor framing', DATE '2019-09-03', DATE '2019-06-17', 1, DATE '2019-06-20', TO_DATE('2019-06-20 20:41:04', 'YYYY-MM-DD hh24:mi:ss'), 0, 100, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'Orange Star', 'cabinet-task', DATE '2018-04-02', DATE '2018-05-15', 1, DATE '2018-05-15', TO_DATE('2018-05-15 15:12:03', 'YYYY-MM-DD hh24:mi:ss'), 0, 101, 100 FROM DUAL
UNION ALL SELECT 511, 'A Inc.', 'A', 'A test lot', 'first floor framing', DATE '2021-05-28', DATE '2021-07-30', 1, DATE '2021-07-30', TO_DATE('2021-07-30 19:03:21', 'YYYY-MM-DD hh24:mi:ss'), 0, 101, 100 FROM DUAL
UNION ALL SELECT 3247, 'B Inc.', 'B', '01/002.B', 'first floor framing', DATE '2021-07-30', DATE '2021-09-20', 1, DATE '2021-09-20', TO_DATE('2021-09-20 16:19:53', 'YYYY-MM-DD hh24:mi:ss'), 0, 101, 100 FROM DUAL;
现在我必须完成最后一项任务。此外，我需要基于所有相同批次的某些条件来sum po_total_amount。然后计算百分比。最后用这些总和和百分比显示最后一个修改的任务。
我提出了以下嵌套查询
Select project_name,
custom_lot_no as "Lot#",
task_name AS "Last Completed Task with a PO",
task_actual_end_date as "Task Actual End Date",
TOTAL_POS_COMPLETED as "Total POs Completed",
TOTAL_COMMITED as "Total Committed",
Round(NVL((TOTAL_POS_COMPLETED / NULLIF(TOTAL_COMMITED, 0)) ,0) * 100) || '%' AS "% Completed"
from(
select project_id,
project_name,
custom_lot_no,
task_name,
task_actual_end_date,
SUM(pos_completed) OVER (PARTITION BY custom_lot_no, TASK_FINISHED) AS TOTAL_POS_COMPLETED,
SUM(committed) OVER (PARTITION BY custom_lot_no, TASK_FINISHED) AS TOTAL_COMMITED,
last_modified_date_time,
last_task_finish_seq
from (
select
project_id,
proj_company_name,
project_name,
custom_lot_no,
task_name,
NVL(to_char(task_actual_end_date), ' ') AS TASK_ACTUAL_END_DATE,
task_finish_date AS TASK_FINISH_DATE,
task_finished AS TASK_FINISHED,
to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') as last_modified_date_time,
ROW_NUMBER() OVER (PARTITION BY custom_lot_no, task_finished ORDER BY to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') desc) AS LAST_TASK_FINISH_SEQ,
po_total_amount,
po_status,
CASE
WHEN po_status = 101 THEN po_total_amount
ELSE 0
END as POs_COMPLETED,
po_type,
CASE
WHEN po_type = 100 THEN po_total_amount
ELSE 0
END as "COMMITTED"
FROM project_complete_data
order by project_name, custom_lot_no, to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') desc
)
where task_finished = 1
order by project_name, custom_lot_no, last_modified_date_time desc
)
where last_task_finish_seq = 1;
但正如您所看到的，有一个嵌套的查询，里面还有另一个查询。虽然它正在得到结果，但它看起来很奇怪。
我可以将其简化为一个嵌套查询吗？或者有其他方法可以在不嵌套的情况下编写相同的查询吗。
事实上，当我试图在里面找到总和时，我陷入了最内部的查询。如果我使用
CASE
WHEN po_status = 101 THEN SUM(po_total_amount) OVER (PARTITION BY custom_lot_no, task_finished ORDER BY to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') desc)
ELSE 0
END as TOTAL_POS_COMPLETED,
然后，对于批次Bldg C1|Parcel 01&*，我在last_task_finish_seq = 5得到总和。如果我在外部查询中使用where last_task_finish_seq = 1。然后我得到和0。因为CCD_ 5的和为0。
如果我在最内部的嵌套查询中或在上面提供的数据上计算总和和百分比。然后一个嵌套查询将自动删除。
如何删除一个嵌套查询或在没有任何子查询的情况下编写此查询？
下面是获取我上面显示的所有数据的实际查询。所以基本上这是我内心最嵌套的查询。我把所有的数据都放在上面的一个表中，这样它就可以很容易地运行了。
select
poh.project_id,
cp.proj_company_name,
cp.project_name,
u.custom_lot_no,
st.task_name,
NVL(to_char(st.original_finish_date), ' ') AS TASK_ACTUAL_END_DATE, -- Task Actual End date
st.finish_date AS TASK_FINISH_DATE,
st.is_finished AS TASK_FINISHED,
st.last_modified_date AS TASK_LAST_MODIFIED_DATE,
to_char(st.last_modified_date, 'YYYY-MM-DD hh24:mi:ss') AS TASK_LAST_MODIFIED_DATE_TIME,
ROW_NUMBER() OVER (PARTITION BY u.custom_lot_no, st.is_finished ORDER BY to_char(st.last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc) AS LAST_TASK_FINISH_SEQ,
poh.po_total_amount,
poh.po_status,
CASE
WHEN (poh.po_status = 101) THEN poh.po_total_amount -- Approved PO
ELSE 0
END as POs_COMPLETED,
poh.po_type,
CASE
WHEN (poh.po_type = 100) THEN poh.po_total_amount -- System Generated PO
ELSE 0
END as "COMMITTED"
FROM po_header poh
inner join unit u on poh.unit_id = u.unit_id -- POs must be created for a lot to populate on this report
inner join schedule s on u.con_schedule_id = s.schedule_id
inner join company_project cp on poh.project_id = cp.project_id
inner join company_project_setting cps on cp.project_id = cps.project_id
inner join po_header_status_lk pohslk on poh.po_status = pohslk.code
LEFT JOIN schedule_task st on poh.schedule_task_id = st.schedule_task_id
LEFT join schedule s on st.schedule_id = s.schedule_id
WHERE poh.company_id = 653
AND poh.po_type = 100 -- Only System Generated POs
AND poh.po_status in ('100', '101') -- PO Must be open (100) and approved (101)
AND u.status_id in ('102', '103') -- Only Include Lots that are Construction Status: In Production (102) or Completed(103)
And s.status = 101 -- A schedule must be approved
AND (
CASE
WHEN (cps.construction_type = 0 and st.is_finished = 1 and (poh.schedule_task_id is not null or poh.schedule_task_id > 0) )THEN -9999
WHEN (cps.construction_type in ('1', '2')) THEN -9999
END
) = -9999
order by cp.project_name, u.custom_lot_no, to_char(st.last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc
感谢
减少到一个子查询
Select project_name,
custom_lot_no as "Lot#",
task_name AS "Last Completed Task with a PO",
task_actual_end_date as "Task Actual End Date",
TOTAL_POS_COMPLETED as "Total POs Completed",
TOTAL_COMMITED as "Total Committed",
Round(NVL((TOTAL_POS_COMPLETED / NULLIF(TOTAL_COMMITED, 0)) ,0) * 100) || '%' AS "% Completed"
from(
select
project_id,
proj_company_name,
project_name,
custom_lot_no,
task_name,
NVL(to_char(task_actual_end_date), ' ') AS TASK_ACTUAL_END_DATE,
task_finish_date AS TASK_FINISH_DATE,
task_finished AS TASK_FINISHED,
to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') as last_modified_date_time,
ROW_NUMBER() OVER (PARTITION BY custom_lot_no, task_finished ORDER BY to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') desc) AS LAST_TASK_FINISH_SEQ,
po_total_amount,
po_status,
CASE
WHEN po_status = 101 THEN po_total_amount
ELSE 0
END as POs_COMPLETED,
SUM(
CASE
WHEN po_status = 101 THEN po_total_amount
ELSE 0
END
) OVER (PARTITION BY custom_lot_no, task_finished) AS TOTAL_POS_COMPLETED,
po_type,
CASE
WHEN po_type = 100 THEN po_total_amount
ELSE 0
END as "COMMITTED",
SUM(
CASE
WHEN po_type = 100 THEN po_total_amount
ELSE 0
END
) OVER (PARTITION BY custom_lot_no, task_finished) AS TOTAL_COMMITED
FROM project_complete_data
order by project_name, custom_lot_no, to_char(task_last_modified_date_time, 'YYYY-MM-DD hh24:mi:ss') desc
)where last_task_finish_seq = 1 and task_finished = 1;