mysql - 交叉表中的 Group_Concat

Question

像这样的表格:

Name   Event     Time
Steve  Start    19:53
Steve  Event1   19:51
Steve  Stop     19:45
Steve  Start    18:13
Steve  Event2   18:01
Steve  Event1   17:51
Steve  Stop     17:45
Bob    Start    19:47
Bob    Event2   19:31
Bob    Stop     17:57

表示发生在开始时间和停止时间之间的事件。我想把它变成:

Name    Start   Stop   Event1    Event2
Steve  19:53    19:45    1         0
Steve  18:13    17:45    1         1
Bob    19:47    17:57    0         1

我的尝试:

select
    name,
    (CASE event WHEN 'start' THEN time ELSE NULL END) AS Start,
    (CASE event WHEN 'stop' THEN time ELSE NULL END) AS Stop,
    (CASE event WHEN 'event1' THEN 1 ELSE 0 END) AS Event1,
    (CASE event WHEN 'event2' THEN 1 ELSE 0 END) AS Event2
from atable

结果是:

Name    Start   Stop    Event1  Event2
Steve   19:53   0   0   0
Steve   0   0   1   0
Steve   0   19:45   0   0
Steve   18:13   0   0   0
Steve   0   0   0   1
Steve   0   0   1   0
Steve   0   17:45   0   0
Bob 19:47   0   0   0
Bob 0   0   1   0
Bob 0   17:57   0   0

如何获取一行中的记录？

我确实尝试了一个 Group_CONCAT:

select
    name,
    GROUP_CONCAT((CASE event WHEN 'start' THEN time ELSE 0 END)) AS Start,
    GROUP_CONCAT((CASE event WHEN 'stop' THEN time ELSE 0 END)) AS Stop,
    GROUP_CONCAT((CASE event WHEN 'event1' THEN 1 ELSE 0 END)) AS Event1,
    GROUP_CONCAT((CASE event WHEN 'event2' THEN 1 ELSE 0 END)) AS Event2
from atable
group by name, event

但结果是:

Name    Start   Stop    Event1  Event2
Bob 0   0   1   0
Bob 19:47   0   0   0
Bob 0   17:57   0   0
Steve   0,0 0,0 1,1 0,0
Steve   0   0   0   1
Steve   19:53,18:13 0,0 0,0 0,0
Steve   0,0 19:45,17:45 0,0 0,0

Answer 1

现在测试:Working SQL FIDDLE

如何获取一行中的记录？

Max 或 min 会组合行，但由于名称不够唯一，我们需要第二种方法来识别组中的组，这将导致输出中出现第二行。我们可以在事件开始时使用用户变量和计数器来做到这一点。

假设:

1. 按时间排序将保留所需的顺序
2. 在同一用户的 event 中多次出现“start”表示结果中需要一个新行。
3. 开始时每组成一行的时间总是最短的。

.

***DDL***
CREATE table atable (
  name varchar(10),
  `event` varchar(10),
  `time` time);

INSERT into atable values 
('Steve',  'Start',    '19:53'),
('Steve',  'Event1',   '19:51'),
('Steve',  'Stop',     '19:45'),
('Steve',  'Start',    '18:13'),
('Steve',  'Event2',   '18:01'),
('Steve',  'Event1',   '17:51'),
('Steve',  'Stop',     '17:45'),
('Bob',    'Start',    '19:47'),
('Bob',    'Event2',   '19:31'),
('Bob',    'Stop',     '17:57');

***DML***
SELECT
    name,
    max(CASE event WHEN 'start' THEN time ELSE NULL END) AS Start,
    max(CASE event WHEN 'stop' THEN time ELSE NULL END) AS Stop,
    max(CASE event WHEN 'event1' THEN 1 ELSE 0 END) AS Event1,
    max(CASE event WHEN 'event2' THEN 1 ELSE 0 END) AS Event2
FROM (SELECT A.*, CASE WHEN @Name=Name and Event = 'Start' THEN @GRP:=@GRP+1
                       WHEN @Name <> Name THEN @GRP:=@GRP+1
                       ELSE @GRP
                  end as mGROUP,
                  CASE WHEN @Name <> NAME then 
                       @Name:=Name
                  END  as Name2
      FROM  atable A
      CROSS JOIN (Select @GRP:=0) x
      CROSS JOIN (Select @Name:='') y
      ORDER BY Name, Time Desc) z
GROUP BY Name, mGROUP

这是如何运作的:内部查询“Z”将两列 mGROUP 和 Name2 添加到基础数据集“atable”。数据库引擎通过交叉连接初始化变量@GRP 和@NAME。我们需要@GRP，这样我们就可以将表中的每个数据子集分组在一起，以便更容易地组合数据。此列代表我们需要的缺失组，以便使用最小值/最大值来组合数据透视表中的行。

@Name 变量用作控制中断方法，因此我们知道何时增加@grp 计数器。当名称不同时，该值将在 Name2 列中重置。 @grp 计数器需要递增两次。一旦遇到“开始”事件，一旦@Name 发生变化。 mGROUP 列为我们做这个增量，当不需要增量时，输出相同的值，因为它是同一组的一部分。 (要查看这项工作，只需运行内部查询“Z”并评估结果)

现在我们有了使用 max 组合组所需的分组机制，我们可以使用简单的聚合来组合通过外部查询完成的行。