1、正常情况。
SELECT JoinEventIds from user
2、使用group_concat函数得到列转行。
select group_concat(JoinEventIds) from user
3、使用SUBSTRING_INDEX和CROSS JOIN将列里面的的数字都拆分出来,把一行变成一列。
方法一(网上查询的方法):
建配置表:
CREATE TABLE digits (digit INT(1));
INSERT INTO digits
VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);
CREATE TABLE sequence (seq INT(3));
INSERT INTO sequence (
SELECT
D1.digit + D2.digit * 10
FROM
digits D1
CROSS JOIN digits D2
);
配置表sequence的结果为0-99的一列数字:
SQL:
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(JoinEventIds, ',', seq),
',' ,- 1
) JoinEventIds
FROM
sequence
CROSS JOIN user
WHERE
seq BETWEEN 1
AND (
SELECT
1 + LENGTH(JoinEventIds) - LENGTH(
REPLACE (JoinEventIds, ',', '')
)
)
方法二(自己不想建表,图省事):将sequence替换为SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) ,user为表名,这张表需要大于100条。
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX(JoinEventIds, ',', seq),
',' ,- 1
) JoinEventIds
FROM
(SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user LIMIT 0,100) b
CROSS JOIN user
WHERE
seq BETWEEN 1
AND (
SELECT
1 + LENGTH(JoinEventIds) - LENGTH(REPLACE(JoinEventIds, ',', '')))
两种方法结果均为: