背景
项目中,有相关取根据距离时间最近/最远的记录需求,于是做了如下的整理,使用时可供参考。
SQL
数据表结构:
CREATE TABLE `emp` (
`id` INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`email` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`gender` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`age` INT(12) NULL DEFAULT NULL,
`create_time` TIMESTAMP NULL DEFAULT NULL
)
ENGINE=InnoDB,COLLATE='utf8_unicode_ci';
插入数据:
INSERT INTO `emp` (`name`, `email`, `gender`, `age`, `create_time`) VALUES
('admin', 'demo@demo.com', '1', 34, '2021-06-21 14:33:54'),
('admin', 'demo@demo.com', '1', 34, '2021-06-23 14:34:19'),
('admin', 'demo@demo.com', '1', 34, '2021-06-29 14:34:51');
查询名称为admin,返回多条记录:
SELECT * FROM `emp` e WHERE e.name = 'admin' ORDER by e.`create_time`;
查询|时间距离最近|的一条:
SELECT * FROM `emp` e WHERE e.name = 'admin' ORDER BY e.create_time DESC LIMIT 0,1;
SELECT * FROM `emp` e WHERE e.name = 'admin' AND e.create_time = ((select max(create_time) from `emp`));
查询|时间距离最远|的一条:
SELECT * FROM `emp` e WHERE e.name = 'admin' ORDER BY e.create_time LIMIT 0,1;
SELECT * FROM `emp` e WHERE e.name = 'admin' AND e.create_time = ((select min(create_time) from `emp`));
limit子句:
limit子句可以被用于强制SELECT语句返回指定的记录数。
limit参数:第1个参数指定第一个返回记录行的偏移量,第2个参数指定返回记录行的最大数目。
初始记录行的偏移量是 0(而不是 1): 为了与PostgreSQL兼容,MySQL也支持句法: limit #OFFSET#。
分页:
查询第1页,每页五条:
SELECT * FROM `emp` e LIMIT 0,2;
查询第2页,每页五条:
SELECT * FROM `emp` e LIMIT 2,2;
Java中
相关代码:
import lombok.Data;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;
public class EmpDemo {
@Data
static class Emp {
private Integer id;
private String name;
private String email;
private String gender;
private Integer age;
private Date createTime;
@Override
public String toString() {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return "Emp{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", gender='" + gender + '\'' +
", age=" + age +
", createTime=" + format.format(createTime) +
'}';
}
}
static List<Emp> getEmpList() {
long currentTimeMillis = System.currentTimeMillis();
List<Emp> empList = new ArrayList<>();
for (int i = 1; i <= 100; i++) {
Emp emp = new Emp();
emp.setId(i);
emp.setName("name");
emp.setAge(i);
currentTimeMillis = currentTimeMillis - Math.abs(new Random().nextInt());
emp.setCreateTime(new Date(currentTimeMillis));
empList.add(emp);
}
return empList;
}
/**
* 时间最近
*
* @param empList
* @return
*/ public static Emp sortedAndFindFirst(List<Emp> empList, boolean asc) {
return empList.parallelStream().sorted((emp1, emp2) -> {
if (emp1.getCreateTime().getTime() < emp2.getCreateTime().getTime()) {
if (asc) {
return -1;
}
return 1;
} else if (emp1.getCreateTime().getTime() > emp2.getCreateTime().getTime()) {
if (asc) {
return 1;
}
return -1;
} else {
return 0;
}
}).findFirst().get();
}
public static Emp sortedAndFindFirst2(List<Emp> empList, boolean asc) {
Collections.sort(empList, new Comparator<Emp>() {
@Override
public int compare(Emp emp1, Emp emp2) {
if (emp1.getCreateTime().getTime() < emp2.getCreateTime().getTime()) {
if (asc) {
return -1;
}
return 1;
} else if (emp1.getCreateTime().getTime() > emp2.getCreateTime().getTime()) {
if (asc) {
return 1;
}
return -1;
} else {
return 0;
}
}
});
return empList.get(0);
}
/**
* @param empList
* @param asc
* @return
*/ public static Emp sortedAndFindFirst3(List<Emp> empList, boolean asc) {
Collections.sort(empList, (emp1, emp2) -> {
if (emp1.getCreateTime().getTime() < emp2.getCreateTime().getTime()) {
if (asc) {
return -1;
}
return 1;
} else if (emp1.getCreateTime().getTime() > emp2.getCreateTime().getTime()) {
if (asc) {
return 1;
}
return -1;
} else {
return 0;
}
});
return empList.get(0);
}
/**
* @param args
*/ public static void main(String[] args) {
List<Emp> empList = getEmpList();
empList.forEach(System.out::println);
System.out.println("距离时间最近的一条:" + sortedAndFindFirst(empList, false));
System.out.println("距离时间最远的一条:" + sortedAndFindFirst3(empList, true));
}
}