Mysql基础语法

- 基础语法

select * from table where id in  (
	select SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY update_time desc), ',', 1 )
	from table GROUP BY unique_key_column);

- 创建数据库
drop database if exists `syntax_db`;
create database if not exists `syntax_db` default charset utf8;
- 创建表
drop table if exists `syntax_db`.`t_log`;
CREATE TABLE `syntax_db`.`t_log` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `unique_key` bigint(11) NOT NULL COMMENT 'id',
  `message` varchar(255) DEFAULT NULL COMMENT '信息',
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志信息表';
- 初始化数据
INSERT INTO `syntax_db`.`t_log` (unique_key,message,`datetime`) VALUES
(1,'信息1','2023-05-19 00:00:00'),
(1,'信息2','2023-05-19 11:11:11'),
(1,'信息3','2023-05-19 22:22:22'),
(2,'信息1','2023-05-19 00:00:00'),
(2,'信息2','2023-05-19 11:11:11'),
(2,'信息3','2023-05-19 22:22:22');

- 语法运用:根据分组，查询最新时间的一条数据
select * from `syntax_db`.`t_log` where id in (
	select SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `datetime` desc), ',', 1 ) 
	from `syntax_db`.`t_log` GROUP BY unique_key);