ltrim函数
根据右边的字符,删除左边出现的字符
语法格式
LTRIM(c1,[,c2])
语法说明
按照c2中的字符,一个一个的截断c1的字符
而且,从左开始执行的,一旦遇到c2中的字符,c1中的字符都会相对应的截断,一直到c1的字符没有c2的字符为止才会结束
RTRIM()函数
RTRIM()与LTRIM()相同,从右边开始执行
SELECT
c1 AS ASSAY_EQUIPMENT_ID,
LTRIM (
SYS_CONNECT_BY_PATH (c2, ','),
','
) ASSAY_ITEM_IDS,
LTRIM (
SYS_CONNECT_BY_PATH (c3, ','),
','
) ASSAY_ITEM_NAMES
FROM
(
SELECT
c1,
c2,
c3,
ROW_NUMBER () OVER (PARTITION BY c1 ORDER BY c2) rn
FROM
(
SELECT
R1.ASSAY_EQUIPMENT_ID c1,
R1.ASSAY_ITEM_ID c2,
T1.ASSAY_ITEM_NAME c3
FROM
CNF_ASSAY_ITEM_EQUI_RELA R1,
DIC_ASSAY_ITEM T1
WHERE
R1.ASSAY_ITEM_ID = T1. ID
)
)
WHERE
CONNECT_BY_ISLEAF = 1 CONNECT BY PRIOR rn + 1 = rn
AND PRIOR c1 = c1 START WITH rn = 1