MySQL 的实现方式:
USE test;
-- 创建数字辅助表
CREATE TABLE test.Num (xh INT PRIMARY KEY);
-- 写入数字辅助表
SET @i = 0;
INSERT INTO test.Num (xh)
SELECT
@i :=@i + 1
FROM
information_schema.`TABLES` a,
information_schema.`TABLES` b
LIMIT 0, 100;
SET @str = 'as,sbsd,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16';
-- 1 单个字符串分割为虚拟表
SELECT
SUBSTRING(str, xh, LOCATE(',', CONCAT(@str, ','), xh) - xh) AS splitstr
FROM
test.Num a,
(SELECT @str AS str) b
WHERE
a.xh <= LENGTH(str)
AND SUBSTRING(CONCAT(',', str), xh, 1) = ',';
-- 2 多个字符串分割为虚拟表
SET @p_DayId = '11,20,30',@p_Num = '40,50,60';
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_DayId, ',', xh), ',', -1) a,
SUBSTRING_INDEX(SUBSTRING_INDEX(@p_Num, ',', xh), ',', -1) b
FROM Num
WHERE xh <= (LENGTH(@p_DayId)-LENGTH(REPLACE(@p_DayId,',','')) +1)
-- 经测试,以上是最快方式;利用集合处理的思想,避免了循环分割。
MSSQL 的实现方式:
USE tempdb;
CREATE TABLE Num (xh INT PRIMARY KEY(xh));
DECLARE @xh INT;
SET @xh = 1
WHILE @xh <= 1000
BEGIN
INSERT INTO Num (xh) SELECT @xh;
SET @xh = @xh + 1;
END
DECLARE @string VARCHAR (1000);
SET @string = 'S,M,LL,XL,XXL,3XL,4XL';
SELECT
t.Splitstr_
FROM
(
SELECT
a.xh,
SUBSTRING(str_, a.xh, charindex(',', str_ + ',', a.xh) - a.xh) AS splitstr_
FROM
Num a,
(SELECT @string AS str_) b
WHERE
a.xh <= len (str_) AND SUBSTRING(',' + str_, xh, 1) = ','
) t