mysql - 如果共享相同的时间戳，则多次选择一列作为单独的列

Question

我有一个表，其中存储了针对通道 ID 的数据。频道 ID 有一个合作伙伴频道 ID，该频道 ID 属于具有自己唯一 ID 的设备，但是频道数据并不总是共享相同的时间戳，并且频道 ID 可能不是增量的。

我想通过使用通道 ID 定义顺序来提取每一对，但显示 DATUM 列两次，每个通道 ID 一次。如果频道 ID 没有该时间段的数据，我想返回“NULL”。

create table DATUM (DATUM_TIMESTAMP varchar(255), DEVICE_ID varchar(255), CHANNEL_ID varchar(255), DATUM varchar(255));
INSERT INTO DATUM VALUES ('2021-08-10T00:00', '1', '1', '10');
INSERT INTO DATUM VALUES ('2021-08-10T00:00', '1', '2', '20');
INSERT INTO DATUM VALUES ('2021-08-10T00:01', '1', '1', '10');
INSERT INTO DATUM VALUES ('2021-08-10T00:02', '1', '2', '20');
INSERT INTO DATUM VALUES ('2021-08-10T00:00', '2', '3', '10');
INSERT INTO DATUM VALUES ('2021-08-10T00:00', '2', '4', '20');
INSERT INTO DATUM VALUES ('2021-08-10T00:01', '2', '3', '10');
INSERT INTO DATUM VALUES ('2021-08-10T00:02', '2', '4', '20');

预期输出：

选择 DATUM_TIMESTAMP、DATUM（其中 CHANNEL_ID = 1）、DATUM（其中 CHANNEL_ID = 2）

--------------------------------
DATUM_TIMESTAMP  | DATUM | DATUM
--------------------------------
2021-08-10T00:00 | 10    | 20
2021-08-10T00:01 | 10    | NULL
2021-08-10T00:02 | NULL  | 20

选择 DATUM_TIMESTAMP，DATUM（其中 CHANNEL_ID = 4），DATUM（其中 CHANNEL_ID = 3）

--------------------------------
DATUM_TIMESTAMP  | DATUM | DATUM
--------------------------------
2021-08-10T00:00 | 20    | 10
2021-08-10T00:01 | NULL  | 10
2021-08-10T00:02 | 20    | NULL

我通过使用 group_concat 接近了，但它不会产生 NULL，我需要使用另一列 DEVICE_ID，并且我需要在 SQL 之外处理输出以拆分 DATUM 列。

select DATUM_TIMESTAMP, DEVICE_ID, group_concat(CHANNEL_ID), group_concat(DATUM order by case when CHANNEL_ID = '1' then '1' when CHANNEL_ID = '2' then '2' else CHANNEL_ID end asc) as 'DATUM' from DATUM where DEVICE_ID = 1 group by DATUM_TIMESTAMP, DEVICE_ID;

标签： mysqlmariadb

Answer 1

这段代码很长，但这可能会对您有所帮助。

SELECT DATUM_TIMESTAMP,
(SELECT datum FROM datum b WHERE channel_id=1 AND a.DATUM_TIMESTAMP=b.DATUM_TIMESTAMP) AS datum,
(SELECT datum FROM datum b WHERE channel_id=2 AND a.DATUM_TIMESTAMP=b.DATUM_TIMESTAMP) AS datum
FROM datum a WHERE (channel_id=1 OR channel_id=2) AND device_id=1 GROUP BY DATUM_TIMESTAMP;

输出

DATUM_TIMESTAMP   datum   datum   
----------------  ------  --------
2021-08-10T00:00  10      20      
2021-08-10T00:01  10      (NULL)  
2021-08-10T00:02  (NULL)  20    

要获得第二个输出，只需channel_id在两个嵌入式选择查询和where主查询的子句中更改。

SELECT DATUM_TIMESTAMP,  
(SELECT datum FROM datum b WHERE channel_id=4 AND a.DATUM_TIMESTAMP=b.DATUM_TIMESTAMP) AS datum,
(SELECT datum FROM datum c WHERE channel_id=3 AND a.DATUM_TIMESTAMP=c.DATUM_TIMESTAMP) AS datum
FROM datum a WHERE (channel_id=4 OR channel_id=3) AND device_id=2 GROUP BY DATUM_TIMESTAMP;

输出

DATUM_TIMESTAMP   datum   datum   
----------------  ------  --------
2021-08-10T00:00  20      10      
2021-08-10T00:01  (NULL)  10      
2021-08-10T00:02  20      (NULL)