时间戳

首页 > 解决方案 > MySQL获取位于给定条件之间的以逗号分隔的记录ID行

mysql - MySQL获取位于给定条件之间的以逗号分隔的记录ID行

问题描述

我很难建立一个逻辑。我有一个 MySQL 表，其数据或多或少类似如下。数据按日期字段排序。

ID      Code  Type  Date 
4       PQR     0   2019-10-17 22:30:00
7       PQR     0   2019-10-17 21:30:00
3       XYZ     0   2019-10-17 20:30:00
1       ABC     1   2019-10-17 19:30:00
5       PQR     0   2019-10-17 18:30:00
6       PQR     0   2019-10-17 17:30:00
2       PQR     0   2019-10-17 16:30:00
8       PQR     0   2019-10-17 15:30:00
9       XYZ     0   2019-10-17 14:30:00
14      ABC     0   2019-10-17 13:30:00
11      XYZ     0   2019-10-17 12:30:00
12      RPM     2   2019-10-17 11:30:00
13      PQR     0   2019-10-17 10:30:00
10      PQR     0   2019-10-17 09:30:00
15      PQR     0   2019-10-17 08:30:00
18      XYZ     0   2019-10-17 07:30:00
17      ABC     1   2019-10-17 06:30:00
16      XYZ     0   2019-10-17 05:30:00
19      XYZ     0   2019-10-17 05:00:00
24      XYZ     0   2019-10-17 04:45:00
21      PQR     0   2019-10-17 04:30:00
26      PQR     0   2019-10-17 04:00:00
23      PQR     0   2019-10-17 03:45:00
20      PQR     0   2019-10-17 03:30:00
25      PQR     0   2019-10-17 03:15:00
22      ABC     0   2019-10-17 03:00:00

我想要完成的是 -

  1. 获取 Code=PQR 的所有行的逗号分隔 ID，在 Code=ABC、Type=0 和 Code=ABC、Type=1 的行之间
  2. 所以结果看起来像 -
| FromID | ToCode |       PQRIds      |
+--------+--------+-------------------+
|  22    |   17   |   21,26,23,20,25  |
+--------+--------+-------------------+
|  17    |   14   |         13,10,15  |
+--------+--------+-------------------+
|  14    |    1   |          5,6,2,8  |

更新

- 在 22-ABC-0 和 17-ABC-1 之间 PQR 很少

- 17-ABC-1 和 14-ABC-0 之间 PQR 很少

- 在 14-ABC-0 和 1-ABC-1 之间 PQR 很少

标签： mysqlsql

解决方案

1) 为每个 abc 分配一个块号和行号 2) 聚合 3) 通过匹配行号找到 id

drop table if exists t;
create table t
(ID   int,   Code varchar(3), Type int,dt datetime);
insert into t (id,code,type,dt) values  
(4    ,   'PQR'  ,   0  , '2019-10-17 22:30:00'),
(7    ,   'PQR'  ,   0  , '2019-10-17 21:30:00'),
(3    ,   'XYZ'  ,   0  , '2019-10-17 20:30:00'),
(1    ,   'ABC'  ,   1  , '2019-10-17 19:30:00'),
(5    ,   'PQR'  ,   0  , '2019-10-17 18:30:00'),
(6    ,   'PQR'  ,   0  , '2019-10-17 17:30:00'),
(2    ,   'PQR'  ,   0  , '2019-10-17 16:30:00'),
(8    ,   'PQR'  ,   0  , '2019-10-17 15:30:00'),
(9    ,   'XYZ'  ,   0  , '2019-10-17 14:30:00'),
(14   ,   'ABC'  ,   0  , '2019-10-17 13:30:00'),
(11   ,   'XYZ'  ,   0  , '2019-10-17 12:30:00'),
(12   ,   'RPM'  ,   2  , '2019-10-17 11:30:00'),
(13   ,   'PQR'  ,   0  , '2019-10-17 10:30:00'),
(10   ,   'PQR'  ,   0  , '2019-10-17 09:30:00'),
(15   ,   'PQR'  ,   0  , '2019-10-17 08:30:00'),
(18   ,   'XYZ'  ,   0  , '2019-10-17 07:30:00'),
(17   ,   'ABC'  ,   1  , '2019-10-17 06:30:00'),
(16   ,   'XYZ'  ,   0  , '2019-10-17 05:30:00'),
(19   ,   'XYZ'  ,   0  , '2019-10-17 05:00:00'),
(24   ,   'XYZ'  ,   0  , '2019-10-17 04:45:00'),
(21   ,   'PQR'  ,   0  , '2019-10-17 04:30:00'),
(26   ,   'PQR'  ,   0  , '2019-10-17 04:00:00'),
(23   ,   'PQR'  ,   0  , '2019-10-17 03:45:00'),
(20   ,   'PQR'  ,   0  , '2019-10-17 03:30:00'),
(25   ,   'PQR'  ,   0  , '2019-10-17 03:15:00'),
(22   ,   'ABC'  ,   0  , '2019-10-17 03:00:00');

Select fromid,toid,gc from
(
select bn,group_concat(case when code = 'pqr' then id end order by dt desc) gc,
         min(rownumber) minrownumber, max(rownumber) + 1 maxrownumber
from
(
select t.*,
         if(code = 'abc', @bn:=@bn+1,@bn:=@bn) bn,
         @rn:=@rn+1 rownumber,
         @p:=code p
from t
cross join (select @bn:=0,@rn:=0,@p:=0) r
order by dt
) s
group by bn 
) t1
join 
(select t.id fromid,t.code fromcode,
         @rn1:=@rn1+1 rownumber
from t
cross join (select @rn1:=0) r
order by dt
) t2
on t2.rownumber = t1.minrownumber
join 
(select t.id toid,t.code tocode,
         @rn2:=@rn2+1 rownumber
from t
cross join (select @rn2:=0) r
order by dt
) t3
on t3.rownumber = t1.maxrownumber;

+--------+------+----------------+
| fromid | toid | gc             |
+--------+------+----------------+
|     22 |   17 | 21,26,23,20,25 |
|     17 |   14 | 13,10,15       |
|     14 |    1 | 5,6,2,8        |
+--------+------+----------------+
3 rows in set (0.12 sec)

推荐阅读