求大神帮忙想下SQL语句，谢谢了

如题所述

第一步，确定中排序
第二步，确定分组排序
第三步，用union all拼接展示形式
第四步，总排序
SQL如下：
select t4.c_ply_app_no, t4.c_rcpt_no
from (

with t1 as (select t.c_ply_app_no, t.c_rcpt_no
from t_fin_plyedr_coldue t
order by t.c_ply_app_no desc, t.c_rcpt_no asc),
t2 as (select t1.c_ply_app_no,
t1.c_rcpt_no,
rownum as row1
from t1),
t3 as (select t2.c_ply_app_no,
t2.c_rcpt_no,
t2.row1,
row_number() over(partition by t2.c_ply_app_no order by t2.c_rcpt_no asc) as row2
from t2)
select t3.c_ply_app_no, t3.c_rcpt_no, t3.row1
from t3
where t3.row2 = 1
union all
select null as c_rcpt_no, t3.c_rcpt_no, t3.row1
from t3) t4
order by t4.row1 asc追问

哥们 报错啊！

追答

报什么错？百度这直接拷贝格式会有问题，要调下，你试试下面的（其实是一样的）

select t4.c_ply_app_no, t4.c_rcpt_no
from (
with t1 as (select t.c_ply_app_no, t.c_rcpt_no
from t_fin_plyedr_coldue t
order by t.c_ply_app_no desc, t.c_rcpt_no asc),
t2 as (select t1.c_ply_app_no,
t1.c_rcpt_no,
rownum as row1
from t1),
t3 as (select t2.c_ply_app_no,
t2.c_rcpt_no,
t2.row1,
row_number() over(partition by t2.c_ply_app_no order by t2.c_rcpt_no asc) as row2
from t2)
select t3.c_ply_app_no, t3.c_rcpt_no, t3.row1
from t3
where t3.row2 = 1
union all
select null as c_rcpt_no, t3.c_rcpt_no, t3.row1
from t3) t4
order by t4.row1 asc

温馨提示：内容为网友见解，仅供参考

当前网址：https://22.t2y.org/zz/20scfxthi.html