承接上篇博客,如果想要数据更加的完美,可以这样去处理。入口
思想:将前面带的别名去掉,仅展示真实的数据,如果数据没有别名直接展示不需要再去处理。
CASE WHEN ELSE END方式处理数据(推荐):
SELECT
t.targetColumn,
CASE
WHEN locate(
'.',
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
)
) > 0 THEN
substring_index(
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
),
'.' ,- 1
)
ELSE
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
)
END AS sourceColumn
FROM
syjxdata t
JOIN mysql.help_topic b ON b.help_topic_id < (
length(t.sourceColumn) - length(
REPLACE (t.sourceColumn, '/', '')
) + 1
)
WHERE
1 = 1
AND t.sourcetable IS NOT NULL
AND t.sourcesystem IS NOT NULL
AND t.sourceColumn IS NOT NULL
AND t.sourceColumn <> ''
ORDER BY
t.targetColumn
IF(str,true,false)函数处理方式(短语句推荐)
SELECT
t.targetColumn,
IF (
locate(
'.',
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
)
) > 0,
substring_index(
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
),
'.' ,- 1
),
substring_index(
substring_index(
t.sourceColumn,
'/',
b.help_topic_id + 1
),
'/' ,- 1
)
)
FROM
syjxdata t
JOIN mysql.help_topic b ON b.help_topic_id < (
length(t.sourceColumn) - length(
REPLACE (t.sourceColumn, '/', '')
) + 1
)
WHERE
1 = 1
AND t.sourcetable IS NOT NULL
AND t.sourcesystem IS NOT NULL
AND t.sourceColumn IS NOT NULL
AND t.sourceColumn <> ''
ORDER BY
t.targetColumn
结果