本文主要是介绍MySql 中的分类查询最后一条数据记录常用的三种方式，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

实例：在某个通话流水记录中提取每个操作员的最后一次操作记录

-- 创建测试表
CREATE TABLE `call_record` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '流水ID',
`user_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '操作员ID',
`call_date` timestamp NULL DEFAULT NULL COMMENT '通话时间',
`detail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '详情记录',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (1, '1001', '2021-04-02 08:43:04', '空号');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (2, '1002', '2021-04-02 08:43:30', '空号');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (3, '1003', '2021-04-02 08:44:38', '无人接听');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (4, '1004', '2021-04-02 08:45:03', '停机');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (5, '1002', '2021-04-02 08:45:30', '关机');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (6, '1001', '2021-04-02 08:45:37', '不接');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (7, '1001', '2021-04-02 08:46:09', '不接');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (8, '1001', '2021-04-02 08:46:17', '关机');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (9, '1004', '2021-04-02 08:46:38', '无人接听');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (10, '1004', '2021-04-02 08:47:16', '不接');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (11, '1004', '2021-04-02 08:47:29', '说自己不是 挂');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (12, '1001', '2021-04-02 08:47:56', '不接');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (13, '1002', '2021-04-02 08:48:20', '无人接听');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (14, '1002', '2021-04-02 08:48:22', '空号');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (15, '1003', '2021-04-02 08:49:02', '空号');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (16, '1004', '2021-04-02 08:49:02', '设置');
INSERT INTO `call_record`(`id`, `user_id`, `call_date`, `detail`) VALUES (17, '1004', '2021-04-02 08:49:27', '停机');

查询全部记录 结果如下

SELECT * FROM `call_record`;

 方式1:

通过给每个操作员的记录依次由时间顺序排序，然后给定每条记录一个编号，通过编号的最大值来取得流水记录中的最后一条记录

-- 第一步，给每个操作员的所有记录依次排序编号
SELECT
    b.user_id,
    b.call_date,
    b.detail,
    @i := ( CASE WHEN @tabId = b.user_id THEN @i + 1 ELSE 1 END ) number,
    ( @tabId := b.user_id ) '' 
FROM
    ( SELECT a.* FROM `call_record` a ORDER BY a.user_id, a.call_date ) b,
    ( SELECT @i := 1, @tabId := '' ) d;

结果如下：

-- 第二步，在通过编号最大值取得最后一条记录
SELECT
    m.* 
FROM
    (
        SELECT
            b.user_id,
            b.call_date,
            b.detail,
            @i := ( CASE WHEN @tabId = b.user_id THEN @i + 1 ELSE 1 END ) number,
            ( @tabId := b.user_id ) '' 
        FROM
            ( SELECT a.* FROM `call_record` a ORDER BY a.user_id, a.call_date ) b,
            ( SELECT @i := 1, @tabId := '' ) d 
    ) m
    LEFT JOIN (
        SELECT
            p.user_id,
            MAX( p.number ) number 
        FROM
            (
                SELECT
                    b.user_id,
                    @i := ( CASE WHEN @tabId = b.user_id THEN @i + 1 ELSE 1 END ) number,
                    ( @tabId := b.user_id ) '' 
                FROM
                    ( SELECT a.* FROM `call_record` a ORDER BY a.user_id, a.call_date ) b,
                    ( SELECT @i := 1, @tabId := '' ) d 
            ) p 
        GROUP BY
            p.user_id 
    ) n ON m.user_id = n.user_id 
WHERE
    m.number = n.number;

结果如下：

 方式2：

通过操作员关联查询，取得查询时间最大的一条记录

SELECT
    a.user_id,
    a.call_date,
    a.detail 
FROM
    call_record a 
WHERE
    ( a.call_date = ( SELECT MAX( call_date ) FROM call_record WHERE user_id = a.user_id ) );

结果如下：

方式3：

和第二种类似

SELECT
    a.user_id,
    a.call_date,
    a.detail 
FROM
    call_record a 
WHERE
    NOT EXISTS ( SELECT 1 FROM call_record WHERE user_id = a.user_id AND call_date > a.call_date ) ;

结果如下：

SELECTa.user_id,a.call_date,a.detail FROMcall_record a WHERENOT EXISTS ( SELECT 1 FROM call_record WHERE user_id = a.user_id AND call_date > a.call_date ) ;

这篇关于MySql 中的分类查询最后一条数据记录常用的三种方式的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！