如何实现sql查询？

Question

比如我查询出来的结果是【一个】字符串“01，02”
如何拆成2条记录？ 即要查询结果为 01 和 02 两条记录
select t.times from t_customer t where rownum=1

只用sql该如何写呢，谢谢

Answer 1

参考SQL

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(test.tag, ',', n.n), ',', -1) tag
   from test  CROSS JOIN tally n
   WHERE n.n <= 1 + (CHAR_LENGTH(test.tag) - CHAR_LENGTH(REPLACE(test.tag, ',', ''))) 

上面的SQL依赖一个tally表。是从test表中，查询tag字段，并按 , 行转列

tally表，你可以使用下面SQL创建：

CREATE TABLE tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO tally (n)
SELECT NULL
 FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
;

参考：https://www.genlei.cn/?p=92