sql - Oracle 选择具有奇数/偶数倍数的 Row_Num
问题描述
我得到以下结果
KICAE 101000000010 110000001
KICAE 101000000010 210000001
KICAE 101000000020 110000002
KICAE 101000000020 210000002
KICAE 101000000030 110000003
KICAE 101000000030 210000003
KICAE 101000000040 110000004
KICAE 101000000040 210000004
当我运行下面的 SQL 时:
SELECT 'KICAE','101'||LPAD(ROW_NUMBER() OVER(ORDER BY COURSE_LIST)*10,9,0),COURSE_LIST FROM SYSADM.PS_CRSE_LST_HDR_SF where COURSE_LIST LIKE '1%'
UNION
SELECT 'KICAE','101'||LPAD(ROW_NUMBER() OVER(ORDER BY COURSE_LIST)*10,9,0),COURSE_LIST FROM SYSADM.PS_CRSE_LST_HDR_SF where COURSE_LIST LIKE '2%'
我想返回以 1 开头的课程列表的奇数倍数和以 2 开头的课程列表的偶数倍数
期望的结果:
KICAE SEQNUM COURSE_LIST
KICAE 101000000010 110000001
KICAE 101000000020 210000001
KICAE 101000000030 110000002
KICAE 101000000040 210000002
KICAE 101000000050 110000003
KICAE 101000000060 210000003
KICAE 101000000070 110000004
KICAE 101000000080 210000004
解决方案
这是我的猜测(COURSES 子查询是用来产生一些输入的,它不是解决方案的一部分):
with courses(course_list) as (
select mod(level-1,2)+1 || '100000' || to_char(ceil(level/2),'fm00')
from dual
where mod(level-1,2)+1 = 1 or ceil(level/2) <= 39
connect by level <= 72*2
)
, dr_added(course_list, dr) as (
select course_list,
dense_rank() over(partition by substr(course_list,1,1) order by course_list) dr
from courses
)
select
101000000000 + dr*20 - case when course_list like '1%' then 10 else 0 end seqnum,
course_list
from dr_added
order by seqnum;
SEQNUM COURSE_LIST
101000000010 110000001
101000000020 210000001
101000000030 110000002
101000000040 210000002
101000000050 110000003
101000000060 210000003
101000000070 110000004
101000000080 210000004
101000000090 110000005
101000000100 210000005
101000000110 110000006
101000000120 210000006
101000000130 110000007
101000000140 210000007
101000000150 110000008
101000000160 210000008
101000000170 110000009
101000000180 210000009
101000000190 110000010
101000000200 210000010
101000000210 110000011
101000000220 210000011
101000000230 110000012
101000000240 210000012
101000000250 110000013
101000000260 210000013
101000000270 110000014
101000000280 210000014
101000000290 110000015
101000000300 210000015
101000000310 110000016
101000000320 210000016
101000000330 110000017
101000000340 210000017
101000000350 110000018
101000000360 210000018
101000000370 110000019
101000000380 210000019
101000000390 110000020
101000000400 210000020
101000000410 110000021
101000000420 210000021
101000000430 110000022
101000000440 210000022
101000000450 110000023
101000000460 210000023
101000000470 110000024
101000000480 210000024
101000000490 110000025
101000000500 210000025
101000000510 110000026
101000000520 210000026
101000000530 110000027
101000000540 210000027
101000000550 110000028
101000000560 210000028
101000000570 110000029
101000000580 210000029
101000000590 110000030
101000000600 210000030
101000000610 110000031
101000000620 210000031
101000000630 110000032
101000000640 210000032
101000000650 110000033
101000000660 210000033
101000000670 110000034
101000000680 210000034
101000000690 110000035
101000000700 210000035
101000000710 110000036
101000000720 210000036
101000000730 110000037
101000000740 210000037
101000000750 110000038
101000000760 210000038
101000000770 110000039
101000000780 210000039
101000000790 110000040
101000000810 110000041
101000000830 110000042
101000000850 110000043
101000000870 110000044
101000000890 110000045
101000000910 110000046
101000000930 110000047
101000000950 110000048
101000000970 110000049
101000000990 110000050
101000001010 110000051
101000001030 110000052
101000001050 110000053
101000001070 110000054
101000001090 110000055
101000001110 110000056
101000001130 110000057
101000001150 110000058
101000001170 110000059
101000001190 110000060
101000001210 110000061
101000001230 110000062
101000001250 110000063
101000001270 110000064
101000001290 110000065
101000001310 110000066
101000001330 110000067
101000001350 110000068
101000001370 110000069
101000001390 110000070
101000001410 110000071
101000001430 110000072
推荐阅读
- ibm-cloud - 如何创建从 github 存储库上的代码部署的 ibm 云功能?
- python - Django在相关外键上组合了许多不相关的模型
- html - 如何更改 Github Pages 主题中的下载按钮路径和文本?
- typescript - 从 TypeScript 接口到类构造函数的快捷语法
- c# - asp.net core中的Request.Body和Request.Content有什么区别?
- amazon-web-services - 在其中一个 lambda 中获取 Step 函数的执行 arn(无服务器框架)
- python - 在 Python 中生成多列值的条件语句
- node.js - 如何阻止 sanitize-html 对 HTML 实体进行编码?
- javascript - 如何在通过 Ajax XMLHttpRequest 发送表单数据的函数上避免 typeError?
- git - Git - 将一个存储库中的现有分支复制到第二个存储库中不存在的分支