本文介绍了如何在oracle中没有数据的列中显示空格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表,在第一个表中存储课程ID,在第二个表中存储课程ID和不同学科领域的描述,如下所示.
I have two tables, in the first table the course id is stored and in the second table the course id and different subject areas description are stored as shown below.
Table PA_CPNT
CPNT_ID( Course ID) Course Title
06201826 AAAA
06201827 BBBB
06201828 CCCC
Table PA_CPNT_SUBJ
CPNT_ID SUBJ_ID
06201826 PLNT_DEV
06201826 WRKS_COUN
06201827 WRKS_COUN1
06201827 WRKS_COUN2
06201827 WRKS_COUN3
06201828 WRKS_COUN
My requirement is to have an output in the below format
CPNT_ID COUrse Title SUBJ_ID1 SUBJ_ID2 SUBJ_ID3
06201826 AAAA PLNT_DEV WRKS_COUN
06201827 BBBB WRKS_COUN1 WRKS_COUN2 WRKS_COUN3
06201828 CCCC WRKS_COUN
我已经编写了以下代码,如何修改此代码以实现上述要求.
I have written the below code, how can I modify this code to achieve the above requirement.
select distinct CPNT_ID,
cpnt_desc,
SUBJ_ID1,
SUBJ_ID2,
SUBJ_ID3
from
(
select a.cpnt_id,
a.cpnt_desc,
b.subj_id as subj_id1,
c.subj_id as subj_id2,
d.subj_id as subj_id3
from PA_CPNT a
inner join PA_CPNT_SUBJ b
on a.cpnt_id=b.cpnt_id
inner join PA_CPNT_SUBJ c
on a.cpnt_id=c.cpnt_id
inner join PA_CPNT_SUBJ d
on a.cpnt_id=d.cpnt_id
) X
where subj_id1 ! = subj_id2
and subj_id2 ! = subj_id3
and subj_id3 ! = subj_id1
请帮助
推荐答案
您可以使用row_number为课程中的每个科目指定一个编号,然后显示科目#1,#2和#3.
You can use row_number to give each subject in a course a number, then show subject #1, #2 and #3.
select
pa_cpnt.cpnt_id,
pa_cpnt.cpnt_desc,
min(case when subj.rn = 1 then subj.subj_id end) as subj_id1,
min(case when subj.rn = 2 then subj.subj_id end) as subj_id2,
min(case when subj.rn = 3 then subj.subj_id end) as subj_id3
from pa_cpnt
left outer join
(
select
cpnt_id,
subj_id,
row_number() over (partition by cpnt_id order by subj_id) as rn
from pa_cpnt_subj
) subj on subj.cpnt_id = pa_cpnt.cpnt_id
group by pa_cpnt.cpnt_id, pa_cpnt.cpnt_desc;
这篇关于如何在oracle中没有数据的列中显示空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!