级联SQL

最新推荐文章于 2026-01-07 10:43:04 发布

原创最新推荐文章于 2026-01-07 10:43:04 发布 · 816 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文通过几个具体的SQL查询案例，展示了如何使用row_number()函数配合partition by进行分组排序，并选取每组的第一条记录，同时涉及了多表关联查询，用于获取如院系、专业、班级等特定信息。

select distinct jx01nd.zymc,zymc from jx01nd where xx0301id = '06' ---院系

select xs0701id, rxnf
   from (select row_number() over(partition by rxnf order by rxnf desc) rn,
                xs0701id,
                rxnf,
                yxsh
           from xs0701) tmp
where rn = 1
---入学年份

select * from jx01nd

select jx01ndid, zymc
   from (select row_number() over(partition by jx01nd.zymc order by jx01nd.zymc desc) rn,
                jx01nd.jx01ndid,
                jx01nd.zymc,
                jx01nd.xx0301id
           from jx01nd) tmp
where rn = 1
    and xx0301id = '06'    ----专业

select XX04ID, Bj
   from (select row_number() over(partition by XX04.Bj order by XX04.Bj desc) rn,
                XX04.XX04ID,
                XX04.Bj,
                XX04.Jx01ndid,
                XX04.Xx0301id,
                XX04.Rxnf
           from XX04) tmp
where rn = 1
    and xx0301id = '06'
    and rxnf = '2006'
    and jx01ndid = 'HRrudNIQO58bNYb9M3LZNvrVEMmmeg59'

----班级