oracle——有分隔符的字段实现列转行
SELECTT .teamName,c.license_plate,regexp_substr(i.mode_id, '[^,]+', 1, level) IDFROMs_team TLEFT JOIN r_team_truck r ON r.team_id = T . IDLEFT JOIN s_truck c ON c. ID = r.truc...
·
SELECT
T .teamName,
c.license_plate,
regexp_substr(i.mode_id, '[^,]+', 1, level) ID
FROM
s_team T
LEFT JOIN r_team_truck r ON r.team_id = T . ID
LEFT JOIN s_truck c ON c. ID = r.truck_id
LEFT JOIN b_intervention_record i ON i.truck_id = c. ID
WHERE
--时间过滤
i.create_time >= TO_DATE ('2018-06-21', 'yyyy-mm-dd')
connect by level <= regexp_count(i.mode_id, ',') + 1
and i.id = prior i.id
and prior dbms_random.value is not null
regexp_substr(ste,reg,起始点,第几次)
regexp_count(i.mode_id, ',')--统计字符串中,的数量
and i.id = prior i.id
and prior dbms_random.value is not null--结束条件
更多推荐
已为社区贡献1条内容
所有评论(0)