获取所有非空字符串，并排序

select BED_CODE from XJX_HIS.HIS_BED t
ORDER BY CASE
             WHEN t.BED_CODE = '' THEN 1
             WHEN REGEXP_LIKE(t.BED_CODE, '^\d+$') THEN CAST(t.BED_CODE AS INTEGER)
             ELSE 999999
             END,
         CASE
             WHEN REGEXP_LIKE(t.BED_CODE, '^\d+$') THEN 0
             ELSE 1
             END,
         t.BED_CODE

根据子节点递归查找父节点

Oracle 达梦 Sql

SELECT SCODE, PARENT_CODE, SNAME, DATA_SPELL, DATA_SNO
FROM XJX_STD.DICT_WS_363_445_DIR
         START WITH SCODE = 'WST363_01_00' -- 从指定节点开始
CONNECT BY PRIOR PARENT_CODE = SCODE -- 递归查找父节点
ORDER BY LEVEL DESC; -- 按层级倒序排列，最上级节点在最前

--- 查询所有叶子节点
select CONNECT_BY_ISLEAF,  t.SCODE, t.SNAME,  LEVEL
from xjx_std.DICT_WS_363_445_DIR t
start with t.PARENT_CODE is   null
connect by   t.PARENT_CODE  =  prior  t.SCODE

关联查询join 子表多条，只关联其中一条

select  V.VISIT_ID,count(V.VISIT_ID) from XJX_HIS.INP_VISIT V
                 left join XJX_HIS.INP_VISIT_BLOOD_OXYGEN_RECORD B on V.VISIT_ID = B.VISIT_ID   and  b.DEL_STATUS != 1 -- 血氧饱和度
                 left join XJX_HIS.HIS_BED H on V.VISIT_BED_ID = H.BED_ID  -- 病床信息
                 LEFT JOIN XJX_MDR.BASE_EMP_INFO BE ON V.NURSE_USER_ID = BE.USER_ID -- 护士信息
        JOIN (SELECT TRANSFER.*,
        ROW_NUMBER() OVER (PARTITION BY TRANSFER.VISIT_ID ORDER BY TRANSFER.OUT_TIME DESC) AS RN
        FROM XJX_HIS.INP_VISIT_DEPT_TRANSFER TRANSFER
        WHERE TRANSFER.DEL_STATUS = 0
        AND TRANSFER.TRANSFER_STATUS = 1
       ) DT ON DT.VISIT_ID = V.VISIT_ID AND DT.RN = 1         -- 转科记录

    group by V.VISIT_ID