MySQL - 返回单行中未确定的列数，与公共(public)键相关

Question

我有很多设备通过网状网络连接。每个设备都有一个序列号。每台设备每小时发送一条消息，说“我还在这里”。服务器记录它没有收到该消息的时间段，我们称之为“陈旧”状态。

设备失效的每个实例都会在我们数据库的“警报”表中创建一个新行。

我想要做的是创建一个报告，该报告只有一行用于每个曾经过时的设备，该设备的每个实例的开始和结束时间戳都是过时的行中的列，继续直到没有更多的陈旧实例。

示例输出:

SERIAL_NUMBER, START, END, START, END, START, END . . .
18462G, 2016-02-03 12:45, 2016-02-03-18:43, 2016-02-05 09:55, 2016-02-07 19:11
T8837G, 2016-02-05 02:34,
7465LT, 2016-02-02 17:45, 2016-02-05 18:13,

在此示例中，设备 18462G 已失效 3 次(意味着它在“警报”表中有 3 行)，但当前未失效。

T8837G 曾经过时(警报表中的 1 行)，仍然过时(无结束日期)。

7465LT 已失效两次(警报表中有 2 行)，但当前处于事件状态。

我在我的 MySQL 中尝试了“GROUP BY”，但它似乎不起作用。

SELECT device.serial_number AS 'device',
location.name AS 'location',
alerting.start AS 'start',
alerting.end AS 'end'
FROM alerting
JOIN device ON alerting.device_id = device.id
JOIN device_location ON device_location.device_id = device.id    
JOIN location ON device_location.location_id = location.id
GROUP BY device
ORDER BY location, device;

Answer 1

一种可能的方式(类似)但与您的示例中的不完全相同是基于 group_concat 和 concat

    SELECT device.serial_number AS device,
    location.name AS location,
    group_concat(concat('start : ',alerting.start,'end : ',alerting.end))
    FROM alerting
    JOIN device ON alerting.device_id = device.id
    JOIN device_location ON device_location.device_id = device.id    
    JOIN location ON device_location.location_id = location.id
    GROUP BY device.serial_number, location.name
    ORDER BY location.name, device.serial_number;