获取最近24小时内具有匹配数据和最大值的记录

xqkwcwgp 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(326)

我有一张table，下面有结构。

CREATE TABLE  notifications (
    `notification_id` int(11) NOT NULL AUTO_INCREMENT,
    `source` varchar(50) NOT NULL,
    `created_time` datetime NOT NULL,
    `not_type` varchar(50) NOT NULL,
    `not_content` longtext NOT NULL,
    `notifier_version` varchar(45) DEFAULT NULL,
    `notification_reason` varchar(245) DEFAULT NULL,
    PRIMARY KEY (`notification_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;

    INSERT INTO `notifications` (`notification_id`,`source`,`created_time`,`not_type`,`not_content`,`notifier_version`,`notification_reason`) VALUES 
    (50,'Asia','2018-05-01 18:10:12','Alert','You are alerted for some Reason','NO_03','Some Reason 1'),
    (51,'Asia','2018-04-29 14:10:12','Alert','You are alerted for some Reason','NO_02','Some Reason 8'),
    (52,'Europe','2018-04-29 10:10:12','Warning','You are Warned for som Reason','NO_02',NULL),
    (53,'Europe','2018-05-01 10:10:12','Warning','You are Warned for som Reason','NO_02',NULL),
    (54,'Europe','2018-04-30 23:10:12','Alert','You are alerted for some Reason','NO_03','Some Reason 1');

我需要与最新的警报接收源列表，在过去24小时内收到的警报数量和通知的版本，发送最后一个警报。
结果中需要的列是，
源-表中不同的示例
通知\u原因-上次引发的通知，如果是在源的24小时之前，则为事件
notifier\u version—引发源的最后一个警报的notifier版本
alert\ u count—源的最近24小时内的警报数。
我尝试了一些东西，因为它是在这个sql小提琴。有人能纠正我并给出解决办法吗

sql mysql

来源：https://stackoverflow.com/questions/50114711/get-records-in-last-24-hours-having-matching-data-and-the-maximum-value

2条答案

按热度按时间

ercv8c1e1#

我想这正是你想要的：

select n.source,
       max(case when na.max_ni = n.notification_id then notification_reason end) as last_alert_reason,
       sum(n.not_type = 'Alert') as alert_count,
       max(case when na.max_ni = n.notification_id then notifier_version end) as last_alert_version 
from notifications n left join
     (select n2.source, max(notification_id) as max_ni
      from notifications n2
      where n2.not_type = 'Alert'
      group by n2.source
     ) na
     on n.source = na.source
group by n.source;

sql小提琴在这里。

赞(0）回复(0）举报 2021-06-21

1u4esq0p2#

您可以使用派生表的思想来获取最近的id和最近24小时的计数。

SELECT
  COUNT(`notification_id`) AS AlertCount,
  MAX(`notification_id`) AS MaxNotification
FROM
  `notifications`
WHERE
  `created_time` BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW()
  AND `not_type` = 'Alert';

然后，加入并筛选：

SELECT
  NotificationTbl.source,
  NotificationTbl.notification_reason,
  NotificationTbl.notifier_version,
  Last24HoursTbl.alert_count
FROM
  `notifications` AS NotificationTbl
INNER JOIN
  (
   SELECT
      COUNT(`notification_id`) AS alert_count,
      MAX(`notification_id`) AS max_notification_id
    FROM
      `notifications`
    WHERE
      `created_time` BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW() 
      AND `not_type` = 'Alert'
  ) AS Last24HoursTbl
  ON NotificationTbl.notification_id = Last24HoursTbl.max_notification_id
  ;

结果（截至回答时间）：

source |    notification_reason | notifier_version | alert_count
------------------------------------------------------------
Europe |    Some Reason 1       | NO_03            | 1

sqlfiddle:http://sqlfiddle.com/#!2014年9月BB6A日

赞(0）回复(0）举报 2021-06-21

我来回答

获取最近24小时内具有匹配数据和最大值的记录

2条答案

相关问题

热门标签

最新问答