带有连接两个表的Oracle PL SQL select查询 [英] Oracle PL SQL select query with join two tables

问题描述

我在Oracle，EmployeeMaster和EmployeeMarks中有两个表，



i想要写下SQl查询获取结果，如下面的快照，



查看



我尝试了什么：



i已完成子查询但不起作用

I have two tables in Oracle, EmployeeMaster and EmployeeMarks,

i want to write SQl query for get result like below snap shot,

View

What I have tried:

i have done with sub query but does not work

推荐答案

是时候了解联接的工作方式，例如： Oracle / PLSQL：加入 [ ^ ]。

It's time to learn how do joins work, see, for instance: Oracle / PLSQL: Joins[^].

你可以用普通的SQL（不是真的需要PL / SQL）来做。对以下查询的更正（如果有）将是最小的，您应该能够自己处理它！

ROW_NUMBER（）是解析函数之一（如sum（..）over，rank（）over，dense_rank（）等）并且不太难 - 投入学习的时间非常值得努力。

You can do it in plain SQL (PL/SQL not really required). Correction (if any) to the following query would be minimal and you should be able to handle it yourself!
ROW_NUMBER() is one of the analytic functions (like sum(..) over, rank() over, dense_rank() over etc) and not too difficult - the time invested in learning is well worth the effort.

select case slno when 1 then name else '' end name, case slno when 1 then to_char(age) else '' end age, marks
from
( 
select name, age, marks, emp_id, row_number() over (partition by emp_id order by mark_id) slno
from
(
select employeename name, a.id emp_id, age, b.id mark_id, marks
from employeemaster a, employeemarks b
where a.id = employeemasterid
)
)
order by emp_id, slno

这篇关于带有连接两个表的Oracle PL SQL select查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！