sql - Oracle SQL - 从另一个表中查找 2 列
问题描述
所以我有2张桌子,
Reg_table
student_ID Course_ID **Register**_status
1 co_01 enrolled
2 co_03 pending
3 co_05 cancelled
4 co_06 enrolled
Compl_table
student_ID Course_ID **Completion**_status
1 co_01 Attended
1 co_03
3 co_05
4 co_06 Attended
4 co_05
6 co_05
我想根据两个表的“Student_ID”和“Course_ID”的组合,将新状态添加到 Reg_table 作为“Final_status”,从“Compl_table”查找。即 *如果学生在 co_01 上“注册”,然后在 co_01 上“参加”,则最终状态应为“参加”;*如果“completion_status”为空或“Compl_table”中不存在“Student_ID”和“Course_ID”的组合,则最终状态应与“Register_status”相同,即“已注册”、“待定”或“已取消” '。
因此,结果表应如下所示:
student_ID Course_ID **Final**_status
1 co_01 Attended
2 co_03 pending
3 co_05 cancelled
4 co_06 Attended
这可能吗?提前致谢。
添加了代码(抱歉比示例复杂一点),我到目前为止
with reg_table as
(select
b.schd_id
,b.DMN_ID
,b.ACT_CPNT_ID
,b.CPNT_TYP_ID
,b.SCHD_DESC
,b.FACILITY_ID
,a.STUD_ID
,a.ENRL_STAT_ID
,a.ENRL_DTE
,c.CMPL_STAT_ID
from
PA_ENROLL_SEAT a
,PA_SCHED b
,pa_cpnt_evthst c
where
a.schd_id = b.schd_id
and
b.ACT_CPNT_ID = c.CPNT_ID(+)
and
a.STUD_ID = c.STUD_ID(+)
)
update reg_table r
set CMPL_STAT_ID = (select CMPL_STAT_ID from pa_cpnt_evthst c where
c.stud_id = a.stud_id and c.CPNT_ID = b.ACT_CPNT_ID)
where exists (select 1
from pa_cpnt_evthst c
where c.stud_id = a.stud_id and
c.CPNT_ID = b.ACT_CPNT_ID and
c.CMPL_STAT_ID is not null
)
解决方案
在 Oracle 中,您可以执行以下操作:
update reg_table r
set register_status = (select c.completion_status
from compl_table c
where c.student_id = r.student_id and
c.course_id = r.course_id
)
where exists (select 1
from compl_table c
where c.student_id = r.student_id and
c.course_id = r.course_id and
c.completion_status is not null
);
推荐阅读
- ionic3 - Default sound not playing in Android v9 but working on Andoird v7 - ionic v3 cordova push plugin
- c++ - 错误:在初始化列表中使用 std::forward
- widget - Qt. Add widgets to QChart, QChartview
- java - Maven 依赖项显示在依赖项树中,但没有导入 jar
- java - 无法使用 19.3 JDBC 驱动程序连接到 Oracle 19.3
- docker - 如何在 Kubernetes 中使用参数运行图像
- javascript - Number Format on Array of Int Jquery
- excel - Copy active cell to last free cell in column A of another sheet
- c# - After payment for payment status api give http 400 error
- javascript - copy text box into clipboard doesn't work