MySQL 5:使用不带LAG / LEAD功能的LAG / LEAD请求

Question

我在一个非常旧的版本(MySQL 5)上遇到了SQL请求的问题。似乎我的需求可以很容易地通过滞后和领先函数来满足，但它们在MySQL 8之前是不存在的，所以这不是一个选择...

下面是一组简化的数据：

CREATE TABLE Tickets (
    incrementalID integer,
    TicketNumber smallint,
    SupportGroup varchar(100)
);
 
INSERT INTO Tickets (incrementalID, TicketNumber, SupportGroup) VALUES 
(345678, 131, 'GSSbdd'),
(347681, 131, 'GSSmdw'),
(347682, 131, 'COPsn1'),
(347683, 131, 'COPsn1'),
(347684, 131, 'COPsn2'),
(342631, 198, 'VIReer'),
(347629, 227, 'LPOdfh'),
(350112, 299, 'COPmwn'),
(350113, 299, 'GSSgdf'),
(350119, 299, 'COPmwn'),
(346784, 714, 'LPOerz'),
(346871, 714, 'GSSwnt'),
(346872, 714, 'GSSunx'),
(346873, 714, 'GSSunx'),
(348931, 714, 'GSSwnt'),
(348941, 714, 'LPOefe'),
(401232, 714, 'LPOefe'),
(401233, 714, 'LPOefe'),
(412344, 714, 'LPOeze'),
(412345, 714, 'LPOeze'),
(416377, 714, 'GSSunx'),
(416378, 714, 'GSSmdw'),
(416379, 714, 'GSSgdf'),
(416380, 714, 'GSSgdf'),
(416381, 714, 'GSSgdf');

我只需要为等同TicketsNumbers选择"SupportGroup“字段从GSS%更改为COP%或COP%更改为GSS%的行

例如，对于TicketNumber 131，我只想选择第2行和第3行，因为SupportGroup从GSS% (GSSmdw)更改为COP% (COPsn1)。

我不想选择在GSS%和另一个GSS%之间有SupportGroup变化的行(例如，在第1行和第2行中，GSSbdd到GSSmdw)

所以最后，预期的结果是：

incrementalID   TicketNumber    SupportGroup
347681          131             GSSmdw
347682          131             COPsn1
350112          299             COPmwn
350113          299             GSSgdf
350119          299             COPmwn

对于信息，我的数据必须先按TicketNumber排序，然后按incrementalID排序，而我对数据库没有写权限，所以我只能读SQL请求。

Answer 1

一种方法是相关子查询。您可以使用重载的having进行过滤：

select t.*,
       (select t2.SupportGroup
        from tickets t2
        where t2.TicketNumber = t.TicketNumber and
              t2.incrementalID < t.incrementalID
        order by t2.incrementalID desc
        limit 1
       ) as prev_SupportGroup,
       (select t2.SupportGroup
        from tickets t2
        where t2.TicketNumber = t.TicketNumber and
              t2.incrementalID > t.incrementalID
        order by t2.incrementalID asc
        limit 1
       ) as next_SupportGroup
from tickets t
having (prev_SupportGroup like 'GSS%' and SupportGroup like 'COP%') or
       (SupportGroup like 'GSS%' and next_SupportGroup like 'COP%');       

Answer 2

SELECT *
FROM Tickets t1
JOIN Tickets t2 ON t1.TicketNumber = t2.TicketNumber
WHERE t1.incrementalID < t2.incrementalID
  AND NOT EXISTS ( SELECT NULL
                   FROM Tickets t3
                   WHERE t1.TicketNumber = t3.TicketNumber
                     AND t1.incrementalID < t3.incrementalID
                     AND t3.incrementalID < t2.incrementalID)
  AND (LEFT(t1.SupportGroup, 3), LEFT(t2.SupportGroup, 3)) IN (('COP', 'GSS'), ('GSS', 'COP'))

fiddle