以前MariaDB版本中的Row_Number()窗口函数

Question

有人知道如何在不支持窗口函数的版本中编写此SQL查询吗？

SELECT *,
ROW_NUMBER() OVER(PARTITION BY tr.ticketId ORDER BY tr.time DESC) AS row_num
FROM tickets AS t
JOIN ticket_responses AS tr
ON tr.ticketId = t.id
WHERE row_num = 1

我发现MYSQL 5.7正在获取行号，但我不知道如何以这种方式使用PARTITION

Answer 1

首先：您的查询不是有效的SQL。您可以在where子句中使用窗口函数，您需要一个子查询：

select *
from (
select *,
row_number() over(partition by tr.ticketid order by tr.time desc) as row_num
from tickets as t
join ticket_responses as tr on tr.ticketid = t.id
) t
where row_num = 1

你可以使用变量来做这件事，但我不建议这样做。MySQL变量有陷阱，并且在MySQL 8.0中宣布了它们未来的弃用。我推荐一个相关的子查询：

select *,
(
select count(*) 
from ticket_responses tr1 
where tr1.ticketid = t.ticketid and tr1.time <= tr.time
) as row_num
from tickets as t
join ticket_responses as tr on tr.ticketid = t.id

这里有几个假设：

• tickets/ticket_responses是一对多关系

• (ticket_id, time)的元组在表ticket_responses中是唯一的

大型数据集可能会影响性能。ticket_responses(ticket_id, time)上的索引可能会有所帮助。

Answer 2

您可以添加一个初始化为零的新迭代器变量，例如

SELECT id, name, ticketId, time
FROM
(
SELECT @row_num := IF(@id = tr.ticketId, @row_num + 1, 1) AS row_num,
@id := tr.ticketId, tr.*
FROM (SELECT @id := 0, @row_num := 0, tr.*, t.name 
FROM ticket_responses AS tr
JOIN tickets AS t
ON tr.ticketId = t.id
ORDER BY ticketId, time DESC) AS tr
) AS tt  
WHERE tt.row_num = 1   

其中IF(@id = tr.ticketId, @rn + 1, 1)部分与
@id := tr.ticketId一起调节分区，同时生成迭代整数

演示

Answer 3

您的查询无效，因为没有为where子句定义row_num。在MariaDB中，您需要一个子查询或CTE。让我假设您过于简化了查询，并且您想要具有最新time的ticket_response。

我建议使用一个相关的子查询：

SELECT *
FROM tickets t JOIN
ticket_responses tr
ON tr.ticketId = t.id
WHERE tr.time = (SELECT MIN(tr2.time)
FROM ticket_responses tr2
WHERE tr2.ticketId = tr.ticketId
);

注意：这并不完全相同。它实际上相当于rank()——但如果time是唯一的，它将返回相同的结果。

如果time可能不是唯一的，那么您需要一些方法来按时分离重复项。如果我假设ticket_responses中有一个唯一的id，那么：

SELECT *
FROM tickets t JOIN
ticket_responses tr
ON tr.ticketId = t.id
WHERE tr.ticket_responses_id = (SELECT tr2.ticket_responses_id
FROM ticket_responses tr2
WHERE tr2.ticketId = tr.ticketId
ORDER BY tr2.time DESC, tr2.ticket_responses_id DESC
LIMIT 1
);