mysql - MySQL将列字符串分为三行

这个查询

SELECT user_Id,CAST(leave_dates_object AS CHAR(10000) CHARACTER SET utf8) AS LeaveDates
FROM `lms_leaves`

退货



现在，我需要格式化的字符串中的三行user_Id和Date和F_or_H从休假日期返回。

我修改并尝试code from this link，但无法获得结果。

预期产量

user_Id Date    LeaveType
85  2016-09-06  F
85  2016-09-07  F
85  2016-09-06  H
63  2016-03-25  F
63  2016-03-02  F
63  2016-03-03  H

请帮我。

最佳答案

假设一条记录中最多有8个日期：

create table users (userId int not null, leaveDates varchar(1000) not null);

INSERT users (userId,leaveDates) VALUES (85,'--- \r-2016-09-06:F\r-2016-09-07:F'),(85,'---\r-2016-09-06:H'),(63,'---\r-2016-03-25:F'),(63,'---\r-2016-03-02:F\r-2016-03-03:H');

SELECT s.userId AS userId,LEFT(s.leaveDate,CHAR_LENGTH(s.leaveDate)-2) AS ldate, RIGHT(s.leaveDate,1) AS lflag
FROM (
  SELECT
    u.userId,
      REPLACE(SUBSTRING(SUBSTRING_INDEX(u.leaveDates, '\r-', n.number),
         CHAR_LENGTH(SUBSTRING_INDEX(u.leaveDates, '\r-', n.number -1)) + 1),
         '\r-', '') as leaveDate
  FROM (SELECT u0.userId,REPLACE(u0.leaveDates,'---','') AS leaveDates FROM users u0) u
  INNER JOIN (SELECT 1 AS number 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) n
  ON (char_length(u.leaveDates) - char_length(replace(u.leaveDates, '\r-', '')) >= n.number-1)) s
 WHERE s.leaveDate<>''
 ORDER BY userId,ldate,lflag;