oracle - 需要给Oracle中的字符串赋值
问题描述
我想为字符串列赋值。下面是包含一个月的休假和工作日数据的列。
Holiday_list (Column name)
--------------------------------------------------
WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH
我想知道列表中的所有日期是工作日和假期。
解决方案
您可以使用递归子查询因式分解子句和SUBSTR
:
WITH holidays ( column_name, dt, is_working, lvl ) AS (
SELECT column_name,
TRUNC( SYSDATE, 'MM' ), -- Replace with the start date for your list
SUBSTR( column_name, 1, 1 ),
1
FROM holiday_list
UNION ALL
SELECT column_name,
dt + INTERVAL '1' DAY,
SUBSTR( column_name, lvl + 1, 1 ),
lvl + 1
FROM holidays
WHERE lvl < LENGTH( column_name )
)
SELECT dt,
is_working
FROM holidays;
其中,对于您的示例数据:
CREATE TABLE Holiday_list (Column_name) AS
SELECT 'WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH' FROM DUAL;
输出:
DT | 正在工作中 :----------------- | :--------- 2021-03-01 00:00:00 | W 2021-03-02 00:00:00 | H 2021-03-03 00:00:00 | H 2021-03-04 00:00:00 | H 2021-03-05 00:00:00 | H 2021-03-06 00:00:00 | H 2021-03-07 00:00:00 | H 2021-03-08 00:00:00 | H 2021-03-09 00:00:00 | W 2021-03-10 00:00:00 | H 2021-03-11 00:00:00 | H 2021-03-12 00:00:00 | H 2021-03-13 00:00:00 | H 2021-03-14 00:00:00 | H 2021-03-15 00:00:00 | H 2021-03-16 00:00:00 | H 2021-03-17 00:00:00 | W 2021-03-18 00:00:00 | H 2021-03-19 00:00:00 | H 2021-03-20 00:00:00 | H 2021-03-21 00:00:00 | H 2021-03-22 00:00:00 | H 2021-03-23 00:00:00 | H 2021-03-24 00:00:00 | H 2021-03-25 00:00:00 | W 2021-03-26 00:00:00 | H 2021-03-27 00:00:00 | H 2021-03-28 00:00:00 | H 2021-03-29 00:00:00 | H 2021-03-30 00:00:00 | H
或者,如果您希望它们都在一行中:
SELECT SUBSTR( column_name, 1, 1 ) AS day01,
SUBSTR( column_name, 2, 1 ) AS day02,
SUBSTR( column_name, 3, 1 ) AS day03,
SUBSTR( column_name, 4, 1 ) AS day04,
SUBSTR( column_name, 5, 1 ) AS day05,
SUBSTR( column_name, 6, 1 ) AS day06,
SUBSTR( column_name, 7, 1 ) AS day07,
SUBSTR( column_name, 8, 1 ) AS day08,
SUBSTR( column_name, 9, 1 ) AS day09,
SUBSTR( column_name, 10, 1 ) AS day10,
SUBSTR( column_name, 11, 1 ) AS day11,
SUBSTR( column_name, 12, 1 ) AS day12,
SUBSTR( column_name, 13, 1 ) AS day13,
SUBSTR( column_name, 14, 1 ) AS day14,
SUBSTR( column_name, 15, 1 ) AS day15,
SUBSTR( column_name, 16, 1 ) AS day16,
SUBSTR( column_name, 17, 1 ) AS day17,
SUBSTR( column_name, 18, 1 ) AS day18,
SUBSTR( column_name, 19, 1 ) AS day19,
SUBSTR( column_name, 20, 1 ) AS day20,
SUBSTR( column_name, 21, 1 ) AS day21,
SUBSTR( column_name, 22, 1 ) AS day22,
SUBSTR( column_name, 23, 1 ) AS day23,
SUBSTR( column_name, 24, 1 ) AS day24,
SUBSTR( column_name, 25, 1 ) AS day25,
SUBSTR( column_name, 26, 1 ) AS day26,
SUBSTR( column_name, 27, 1 ) AS day27,
SUBSTR( column_name, 28, 1 ) AS day28,
SUBSTR( column_name, 29, 1 ) AS day29,
SUBSTR( column_name, 30, 1 ) AS day30,
SUBSTR( column_name, 31, 1 ) AS day31
FROM holiday_list
哪个输出:
DAY01 | DAY02 | DAY03 | DAY04 | DAY05 | DAY06 | DAY07 | DAY08 | DAY09 | DAY10 | DAY11 | 第十二天 | 第十三天 | DAY14 | DAY15 | DAY16 | DAY17 | DAY18 | DAY19 | DAY20 | DAY21 | DAY22 | DAY23 | DAY24 | DAY25 | DAY26 | DAY27 | DAY28 | DAY29 | DAY30 | DAY31 :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- W | H | H | H | H | H | H | H | W | H | H | H | H | H | H | H | W | H | H | H | H | H | H | H | W | H | H | H | H | H | 无效的
db<>在这里摆弄
推荐阅读
- python - 将 depot_tools 添加到 PATH 的开头(必须在任何 Python 和/或 git 安装之前)
- camera - 视口在 libgdx 中如何工作以及如何正确设置它?
- java - PowerMockito.verifyPrivate 始终独立于参数工作
- javascript - 棘手的对象展平
- mysql - 用于比较两个表并在另一个表中插入匹配的 Mysql 查询
- jenkins - Jenkins 声明式管道:检查以前的构建状态
- java - 存储过程连接实体 Hibernate/Spring 数据
- flutter - Dart:按频率排序字符串列表
- python - 如何通过字符串创建离散的颜色条作为时间和高度图?
- azure - Microsoft.ApplicationInsights.AzureWebSites 冲突