mysql - 使用 GROUP_CONCAT 更新多个列未按预期工作

Question

帮助需要的人!我有一个存储的过程，可以从另一个日历样式表中更新列。第二个记录应该存储在根据日期和个人分组的日历中，但是我无法通过单个 UPDATE 语句实现它；我不得不进行这些丑陋的一系列逐列更新。如果您有任何想法，我们将不胜感激。代码如下:

DECLARE thisYearMonth VARCHAR(7);
SET thisYearMonth = '2016-09';

UPDATE calendar CAL
INNER JOIN (
    SELECT checkinout.userid,
        checkinout.checktime,
        GROUP_CONCAT(checkinout.checktime) ORDER BY checkinout.id SEPARATOR ' | ') AS checktime
    FROM checkinout
    INNER JOIN calendar ON calendar.userid = checkinout.userid
    WHERE checkinout.userid = calendar.userid
        AND DATE(checktime) = CONCAT(thisYearMonth, '-', '01')
    GROUP BY userid
) CHK ON CHK.userid = CAL.userid 
SET CAL.day1 = CHK.checkstatus;

UPDATE calendar CAL
INNER JOIN (
    SELECT checkinout.userid,
        checkinout.checktime,
        GROUP_CONCAT(checkinout.checktime ORDER BY checkinout.id SEPARATOR ' | ') AS checktime
    FROM checkinout
    INNER JOIN calendar ON calendar.userid = checkinout.userid
    WHERE checkinout.userid = calendar.userid
        AND DATE(checktime) = CONCAT(thisYearMonth, '-', '02')
    GROUP BY userid
) CHK ON CHK.userid = CAL.userid 
SET CAL.day2 = CHK.checkstatus;

依此类推，直到第 n 天。问题是我如何让它像单个 UPDATE 一样工作，不会在所有剩余日历行中重复与行“day1”相同的值，即 day1 获取 10:00，day2 获取 10:00 等等。以下是生成此行为的代码:

UPDATE calendar CAL
INNER JOIN (
    SELECT checkinout.userid,
        checkinout.checktime,
        GROUP_CONCAT(checkinout.checktime) ORDER BY checkinout.id SEPARATOR ' | ') AS checktime
    FROM checkinout
    INNER JOIN calendar ON calendar.userid = checkinout.userid
    WHERE checkinout.userid = calendar.userid
        AND DATE(checktime) = CONCAT(thisYearMonth, '-', '01')
    GROUP BY userid
) CHK ON CHK.userid = CAL.userid 
SET CAL.day1 = CHK.checkstatus,
    CAL.day2 = CHK.checkstatus,
    CAL.day3 = CHK.checkstatus
...;

编辑:根据 Gordon Linoff 的说法，这可以使用条件聚合来实现，但是它每行只带来一条记录，而不是每天与每个用户 ID 相对应的所有记录。

像这样:

Results using conditional aggregation

预期结果:

Result updating row by row individually

我将在下面的代码中发布此示例数据，以便您验证我的结果

DROP TABLE IF EXISTS tmp_checkinout;

CREATE TEMPORARY TABLE tmp_checkinout
 (`id` int, `userid` int, `checktime` datetime, `checkstatus` varchar(8));

INSERT INTO tmp_checkinout
 (`id`, `userid`, `checktime`, `checkstatus`)
VALUES
 (1 , 3, '2016-8-1 07:49:23', 'SHRP'),
 (2 , 2, '2016-8-1 08:01:40', 'SHRP'),
 (3 , 1, '2016-8-1 08:49:07', 'SHRP'),
 (4 , 5, '2016-8-1 09:14:19', 'LATE'),
 (5 , 3, '2016-8-2 07:48:06', 'SHRP'),
 (6 , 2, '2016-8-2 08:04:03', 'SHRP'),
 (7 , 4, '2016-8-2 08:04:12', 'SHRP'),
 (8 , 1, '2016-8-2 08:49:07', 'SHRP'),
 (9 , 5, '2016-8-2 09:10:31', 'TOLR'),
 (10, 3, '2016-8-2 10:40:16', 'EXTN'),
 (11, 3, '2016-8-3 07:48:32', 'SHRP'),
 (12, 2, '2016-8-3 08:05:34', 'SHRP'),
 (13, 4, '2016-8-3 08:12:23', 'LATE'),
 (14, 5, '2016-8-3 09:08:52', 'TOLR'),
 (15, 1, '2016-8-3 10:23:41', 'EXTN'),
 (16, 3, '2016-8-4 07:49:15', 'SHRP'),
 (17, 2, '2016-8-4 08:05:39', 'SHRP'),
 (18, 1, '2016-8-4 08:36:44', 'SHRP'),
 (19, 4, '2016-8-4 08:07:22', 'TOLR'),
 (20, 5, '2016-8-4 09:22:34', 'LATE'),
 (21, 3, '2016-8-5 07:51:42', 'SHRP'),
 (22, 4, '2016-8-5 08:11:33', 'LATE'),
 (23, 2, '2016-8-5 08:16:54', 'LATE'),
 (24, 1, '2016-8-5 08:53:20', 'SHRP'),
 (25, 5, '2016-8-5 09:26:02', 'LATE'),
 (26, 3, '2016-8-2 10:52:44', 'EXTN')
;

DROP TABLE IF EXISTS tmp_calendar;

CREATE TEMPORARY TABLE tmp_calendar
 (`userid` int, `day1` varchar(40), `day2` varchar(40), `day3` varchar(40), `day4` varchar(40), `day5` varchar(40));

INSERT INTO tmp_calendar
 (`userid`)
VALUES (1), (2), (3), (4), (5);

使用 Gordon Linoff 的答案，这是生成如图 1 所示结果的代码:

UPDATE tmp_calendar AS cal
INNER JOIN
 (SELECT id, userid,
  CASE WHEN DAY(checktime) = 1 THEN CONCAT('ID:', id, ', ', checkstatus, ', ', checktime) ELSE NULL END AS timeandstatus_01,
  CASE WHEN DAY(checktime) = 2 THEN CONCAT('ID:', id, ', ', checkstatus, ', ', checktime) ELSE NULL END AS timeandstatus_02,
  CASE WHEN DAY(checktime) = 3 THEN CONCAT('ID:', id, ', ', checkstatus, ', ', checktime) ELSE NULL END AS timeandstatus_03,
  CASE WHEN DAY(checktime) = 4 THEN CONCAT('ID:', id, ', ', checkstatus, ', ', checktime) ELSE NULL END AS timeandstatus_04,
  CASE WHEN DAY(checktime) = 5 THEN CONCAT('ID:', id, ', ', checkstatus, ', ', checktime) ELSE NULL END AS timeandstatus_05
 FROM tmp_checkinout
 WHERE DATE_FORMAT(checktime, '%Y-%m') = '2016-08'
 ORDER BY id ASC) AS chk ON chk.userid = cal.userid
SET cal.day1 = chk.timeandstatus_01,
 cal.day2 = chk.timeandstatus_02,
 cal.day3 = chk.timeandstatus_03,
 cal.day4 = chk.timeandstatus_04,
 cal.day5 = chk.timeandstatus_05
WHERE cal.userid = chk.userid;

感谢您的指点:-)

Answer 1

我认为你需要在子查询中进行条件聚合:

UPDATE calendar CAL INNER JOIN
       (SELECT ch.userid, ch.checktime,
               GROUP_CONCAT(CASE WHEN DAY(checktime) = 01 THEN ch.checktime END ORDER BY ch.id SEPARATOR ' | ') AS checktime_01,
               GROUP_CONCAT(CASE WHEN DAY(checktime) = 02 THEN ch.checktime END ORDER BY ch.id SEPARATOR ' | ') AS checktime_02,
               . . .
        FROM checkinout ch INNER JOIN
             calendar ca
             ON ca.userid = ch.userid
        WHERE date_format(checktime, '%Y-%m') = thisYearMonth
        GROUP BY userid
       ) CHK 
       ON CHK.userid = CAL.userid 
    SET CAL.day1 = CHK.checkstatus_01,
        CAL.day2 = CHK.checkstatus_02;