背景与意义:某学长写出了一段又臭又长的代码,引发了我的思考
获取12个月的月份:
month |
---|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
正常操作:
SELECT MONTH
FROM
(
SELECT
1 MONTH UNION ALL
SELECT
2 MONTH UNION ALL
SELECT
3 MONTH UNION ALL
SELECT
4 MONTH UNION ALL
SELECT
5 MONTH UNION ALL
SELECT
6 MONTH UNION ALL
SELECT
7 MONTH UNION ALL
SELECT
8 MONTH UNION ALL
SELECT
9 MONTH UNION ALL
SELECT
10 MONTH UNION ALL
SELECT
11 MONTH UNION ALL
SELECT
12 MONTH
) months;
因为实在是太长了,希望能简短一点,因此调用MySQL的help_topic进行改写:
MySQL版本
SELECT
substring_index( substring_index( m_str, m_split, help_topic_id + 1 ), m_split,- 1 ) AS MONTH
FROM
mysql.help_topic,
( SELECT '1,2,3,4,5,6,7,8,9,10,11,12' AS m_str, ',' AS m_split FROM DUAL ) AS m_tbl
WHERE
help_topic_id < ( length( m_tbl.m_str ) - length( REPLACE ( m_tbl.m_str, m_split, '' )) + 1 );
使用了拆分字符串的思想,其中m_str就是每行需要显示的数值,m_split 是分隔符。
输出结果
Oracle版:
SELECT
ROWNUM MONTH
FROM
DUAL CONNECT BY LEVEL <= 12;