duplicates - 解码或 NVL,我试图避免重复
问题描述
我试图避免重复记录。我希望 sql 拉出任何拥有 ('AU','RE', 'RW') 记录的人,但我只需要一个显示在我的结果中。你可以使用 Decode 或 Nvl 来做这样的事情吗?
select distinct r1.sfrstcr_rsts_code, spriden_last_name, spriden_first_name, s1.saradap_appl_date, s1.saradap_appl_no
from saradap s1, sfrstcr r1, spriden
where spriden_pidm = sfrstcr_pidm
and spriden_pidm = saradap_pidm
and s1.saradap_appl_no = (select max( s2.saradap_appl_no)
from saradap s2
where s2.saradap_pidm = s1.saradap_pidm)
and sfrstcr_pidm (+) = saradap_pidm
and saradap_term_code_entry = sfrstcr_term_code
and r1.sfrstcr_rsts_code in ('AU','RE', 'RW')
and r1.sfrstcr_reg_seq = (select max (r2.sfrstcr_reg_seq)
from sfrstcr r2
where r2.sfrstcr_reg_seq = r1.sfrstcr_reg_seq)
AND saradap_term_code_entry = 202210
--and stvmajr_code = saradap_term_code_entry
order by spriden_last_name
解决方案
---------------------------------------------------------------------------------------
--This may be a place to start.
--Note: This query returns all students who meet the following criteria:
--1. At least one record in SARADAP where SARADAP_TERM_ENTRY = SFRSTCR_TERM_CODE
--2. At least one record in SPRIDEN where SPRIDEN_CHANGE_IND is null
--3. At least one record in SFRSTCR where RSTS_CODE in AU,RE,RW
--It doesn't return all the distinct RSTS_CODE in SFRSTCR.
--It does check that a record is in SFRSTCR for TERM=202210 and reports the RSTS_CODE for the MAX(CRN)
--To get an understanding of how this query works, I suggest running the INNER query first.
---------------------------------------------------------------------------------------
select sfrstcr_rsts_code,
spriden_last_name,
spriden_first_name,
saradap_appl_date,
saradap_appl_no
from (
----------------------------------------------------------------------------
--INNER QUERY: Returns all related SARADAP records and sorts by APPL_NO
-- Returns all related SFRSTCR records and sorts by CRN
----------------------------------------------------------------------------
select sfrstcr_pidm, --Useful for spot checking
sfrstcr_rsts_code,
spriden_last_name,
spriden_first_name,
saradap_appl_date,
saradap_appl_no,
--SARADAP has multiple rows. You specified max(appl_no) as record of choice Use RANK() which ranks record by appl_no
rank() over (partition by saradap_pidm order by saradap_appl_no desc) as max_saradap_record,
--SFRSTCR has multiple rows. The WHERE clause filters by AU,RE,RW. Rank here sorted by CRN arbitrarly ranks
--Since you just want students who have at least one registration record in SFRSTCR you can sort by CRN and get max crn.
--However it will report the RS code just for this SFRSTCR record.
rank() over (partition by sfrstcr_pidm order by sfrstcr_crn desc) as max_crn
from sfrstcr inner join saradap on sfrstcr_pidm = saradap_pidm and
sfrstcr_term_code = saradap_term_code_entry
--SPRIDEN has many rows. Use WHERE CHANGE_IND = null as it is the record of choice for SPRIDEN
inner join spriden on sfrstcr_pidm = spriden_pidm
where sfrstcr_term_code = '202210'
and spriden_change_ind is null
and sfrstcr_rsts_code in ('AU','RE','RW')
-------END INNER QUERY
)
where max_saradap_record=1 and
max_crn=1;
推荐阅读
- c - 函数指针转换为 void 而不被调用
- android - (操作系统错误:访问被拒绝。,errno = 5)颤振运行
- http - 是否有必要读取并关闭响应主体以重试 TCP 连接?
- python - 继续在这个简单的 Python 函数中似乎不起作用
- python - 缺少 1 个必需的位置参数:'first'
- python - 根据“if”语句打开 tkinter 窗口
- swift - 在扩展中,如何使用占位符类型定义计算属性?
- flutter - 显示来自数据库的插入数据时,FutureBuilder 中出现 UnimplementedError 错误
- android - 为不同的 API 资源创建另一个 PagingSource?
- python - 在 Plotly 中为时间图添加过滤器