Chr☆s Kwok 的技术笔记

.NET, C#, WPF, WCF, WF, .NetCore & LINQ ... I know how it works because I know why it works ...

博客园 首页 新随笔 订阅 管理
  130 随笔 :: 0 文章 :: 30 评论 :: 30万 阅读
SQL SERVER 将ID串列转换为多行的写法
select distinct a.employeeid as MANAGEREMPLOYEEID
  , CAST(SUBSTRING(a.AttributeValue,number,CHARINDEX(',',a.AttributeValue+',',number)-number) AS INT) as MEMBEREMPLOYEEID
from dbo.tbEmployeeAttribute a, master..spt_values
where number >= 1 
  and number < len(a.AttributeValue)
  and a.AttributeFlag = 15
  and SUBSTRING(','+a.AttributeValue,number,1) = ',';
原表数据:
0
执行结果如下:
0
 
ORACLE 将ID串列转换为多行的写法:
CREATE OR REPLACE VIEW ROLE.TEAMEMEMBER AS
SELECT a.employeeid MANAGEREMPLOYEEID, b.column_value MEMBEREMPLOYEEID
FROM role.employeeattribute a
     , table(CONCEPT.fnIDInString(a.attributevalue,',')) b
WHERE a.attributecode = 15;
原表数据:
0
SQL执行转换之后:
0
 
附录:CONCEPT.fnIDInString 自定义函数
CREATE OR REPLACE FUNCTION CONCEPT.fnIDInString(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN  type_str_split  
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END ;

日期、时间戳和字符串互转格式:

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-------------------------------------------------------
2023-02-14 11:44:27

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SSxFF') FROM DUAL;
-------------------------------------------------------
ORA-01821: 日期格式无法识别

SQL> SELECT TO_CHAR(current_timestamp(5),'YYYY-MM-DD HH24:MI:SSxFF') FROM DUAL;
-------------------------------------------------------
2023-02-14 11:43:45.89415

SQL> SELECT TO_DATE('2023-02-14 11:04:28','yyyy-mm-dd HH:mi:ss') FROM DUAL;
-------------------------------------------------------
2023/2/14 11:04:28

SQL> SELECT TO_DATE('2023-02-14 11:39:26.34183','YYYY-MM-DD HH24:MI:SSXFF') FROM DUAL;
-------------------------------------------------------
ORA-01821: 日期格式无法识别

SQL> SELECT TO_TIMESTAMP('2023-02-14 11:39:26.34183','YYYY-MM-DD HH24:MI:SSXFF') FROM DUAL;
-------------------------------------------------------
14-2-23 11.39.26.341830000 上午

SQL> SELECT TO_TIMESTAMP('2023-02-14 11:39:26','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-------------------------------------------------------
14-2-23 11.39.26.000000000 上午

 

posted on   Chr☆s  阅读(249)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2011-09-19 在SilverLight4中如何让TextBox的IsReadOnly属性为True时的背景色仍然是白色(透明)的呢?
点击右上角即可分享
微信分享提示