时间戳

首页 > 解决方案 > 长 SQL 查询中的日期之间

mysql - 长 SQL 查询中的日期之间

问题描述

我有一个查询，我在其中获取 USER ID 和表“tickets”中最常见的用户 ID 的行数。但是现在，我想做同样的查询，但它需要在我插入的 2 个日期时间之间进行搜索。（有一列带有日期时间值（'created_at'））

这是我的代码：

SELECT created_by,COUNT(*) as tickets_count
FROM tickets
WHERE created_at
group by created_by having count(*) =(select max(tickets_count) from (select created_by,count(*) as tickets_count from tickets group by created_by) tickets)

这是表格：

点击图片

我已经尝试过：

SELECT created_by, COUNT(*) as tickets_count
FROM tickets
WHERE created_at BETWEEN '2021-07-25 10:00:00' AND ' 2021-07-30 12:00:00'
group by created_by
having count(*) =(select max(tickets_count) from (select created_by,count(*) as tickets_count from tickets group by created_by) tickets)

但它给了我 0 行，这是不正确的，因为 ID 6 介于这 2 天之间。

标签： mysqlsql

解决方案

如果您只寻找一个用户，您可以使用ORDER BYand LIMIT：

SELECT created_by, COUNT(*) as tickets_count
FROM tickets
WHERE created_at BETWEEN '2021-07-25 10:00:00' AND ' 2021-07-30 12:00:00'
GROUP BY created_by
ORDER BY COUNT(*) DESC
LIMIT 1;

如果您希望所有用户都存在重复项，请使用窗口函数：

SELECT t.*
FROM (SELECT created_by, COUNT(*) as tickets_count,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM tickets
      WHERE created_at BETWEEN '2021-07-25 10:00:00' AND ' 2021-07-30 12:00:00'
      GROUP BY created_by
     ) t
WHERE seqnum = 1;

推荐阅读