MySQL 5:使用不带LAG/LEAD函数的LAG/LEA德请求

Question

我在一个非常旧的版本(MySQL 5(上处理SQL请求时遇到问题。LAG和LEAD函数似乎可以很容易地满足我的需求，但它们在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');

并且我只需要选择具有"；SupportGroup"；字段从GSS%更改为COP%或COP%更改为GSS%，用于标识TicketsNumbers

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

我不想选择支持组在GSS%和另一个GSS%之间变化的行(例如，在第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'))

小提琴