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

-- 创建测试表
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 ) ;