>我有一个下面结构的表格。
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 小时内收到的警报数以及发送上次警报的通知版本。
我在结果中需要的列是,
- 源 - 表中的不同实例
- notification_reason - 引发的最后通知,如果源在 24 小时之前,则为事件
- notifier_version - 导致源发出最后一个警报的 Notfier 版本
- alert_count - 源过去 24 小时内的警报数。
我尝试了这个SQL小提琴中的东西。有人可以纠正我并提供解决方案
可以使用派生表概念获取过去 24 小时的最新 ID 和计数。
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/#!9/14bb6a/14
我认为这可以满足您的需求:
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 小提琴在这里。