基于字段值的MySql查询uniq记录
基于字段值的MySql查询uniq记录
提问于 2017-02-01 02:02:56
我已经设置了告警数据，我想要的是获得最新的未完成告警。
CREATE TABLE `alarm` (
`alarmId` int(10) unsigned NOT NULL,
`dT` time(6) DEFAULT NULL,
`sevId` int(10) unsigned DEFAULT NULL,
`alarmText` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of alarm
-- ----------------------------
INSERT INTO `alarm` VALUES ('1', '00:48:05.000000', '1', 'alarm 1');
INSERT INTO `alarm` VALUES ('1', '01:48:17.000000', '2', 'alarm 1');
INSERT INTO `alarm` VALUES ('1', '02:48:34.000000', '3', 'alarm 1');
INSERT INTO `alarm` VALUES ('2', '00:48:00.000000', '2', 'alarm 2');
INSERT INTO `alarm` VALUES ('2', '00:52:07.000000', '1', 'alarm 2');
INSERT INTO `alarm` VALUES ('3', '16:49:24.000000', '2', 'alarm 3');显示没有sevId 3的最新告警(清除)，到目前为止我尝试的如下，结果很好，除了alarmId 2应该只返回1个告警，
SELECT
*
FROM alarm
WHERE
alarmId NOT IN ( SELECT alarmId FROM alarm WHERE sevId = '3'
)
-> ;
+---------+-----------------+-------+-----------+
| alarmId | dT | sevId | alarmText |
+---------+-----------------+-------+-----------+
| 2 | 00:48:00.000000 | 2 | alarm 2 |
| 2 | 00:52:07.000000 | 1 | alarm 2 |
| 3 | 16:49:24.000000 | 2 | alarm 3 |
+---------+-----------------+-------+-----------+预期结果
+---------+-----------------+-------+-----------+
| alarmId | dT | sevId | alarmText |
+---------+-----------------+-------+-----------+
| 2 | 00:52:07.000000 | 1 | alarm 2 |
| 3 | 16:49:24.000000 | 2 | alarm 3 |
+---------+-----------------+-------+-----------+回答 1
Stack Overflow用户
回答已采纳
发布于 2017-02-01 02:06:40
您可以尝试在子查询中查找dT的最大值，并将其与主表连接，以获得仅具有最大dT的行。
SELECT
a.*
FROM
alarm a
INNER JOIN
(SELECT
alarmId, MAX(dT) dT
FROM
alarm
GROUP BY alarmId) b ON a.alarmId = b.alarmId AND a.dT = b.dT
WHERE
a.alarmId NOT IN (SELECT
alarmId
FROM
alarm
WHERE
sevId = '3')不使用group by也不需要使用更快的方法：
SELECT
a.*
FROM
alarm a
LEFT JOIN
alarm b ON a.alarmId = b.alarmId AND a.dT < b.dT
WHERE
b.alarmId IS NULL
AND a.alarmId NOT IN (SELECT
alarmId
FROM
alarm
WHERE
sevId = '3')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41964450
复制相关文章
相似问题
腾讯云开发者
