常用工具和SQL

实际工作中有些常用的工具或SQL或API等。

1.根据身份证号计算年龄：

SELECT YEAR (
		CURDATE()) - SUBSTRING( '身份证号', 7, 4 ) - (
	RIGHT ( CURDATE(), 5 ) < SUBSTRING( '身份证号', 11, 4 )) 

特点是严格按照年月日计算的年龄

2.根据创建时间统计每月新增记录数

SELECT 
    YEAR(create_time) AS `year`,
    MONTH(create_time) AS `month`,
    COUNT(*) AS `count`
FROM 
    `your_table_name`
WHERE YEAR(create_time) = 2024
GROUP BY 
    `year`,`month`
ORDER BY 
    `year`,`month`
;

3.根据自增ID和创建时间查询每个用户的最新的记录

逻辑：先找出每个 user_id 对应的最新 create_time 和 id，然后再根据这些找到最新的记录

SELECT t1.*
FROM `your_table_name` t1
JOIN (
    SELECT user_id, MAX(create_time) AS max_create_time, MAX(id) AS max_id
    FROM `your_table_name`
    GROUP BY user_id
) t2
ON t1.user_id = t2.user_id AND t1.create_time = t2.max_create_time AND t1.id = t2.max_id;

创建适当的索引

CREATE INDEX idx_uid_ctime_id ON `your_table_name`(user_id, create_time, id);

4.MyBatis 批量更新

<update id="updateBatchProjectTrainExamInfos">
        update project_train_exam pte set pte.update_time=NOW()
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.trainDay != null">
                    pte.train_day = case when
                    pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.trainDay}
                    end
                </if>
            </foreach>
        </trim>
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.trainFlag != null">
                    pte.train_flag = case when
                    pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.trainFlag}
                    end
                </if>
            </foreach>
        </trim>
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.examDay != null">
                    pte.exam_day = case when pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.examDay}
                    end
                </if>
            </foreach>
        </trim>
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.examFlag != null">
                    pte.exam_flag = case when pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.examFlag}
                    end
                </if>
            </foreach>
        </trim>
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.trainUpDay != null">
                    pte.train_up_day = case when pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.trainUpDay}
                    end
                </if>
            </foreach>
        </trim>
        <trim prefix="," prefixOverrides="AND">
            <foreach collection="infos" index="index" item="info" separator=",">
                <if test="info.examUpDay != null">
                    pte.exam_up_day = case when pte.id in
                    <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
                        #{info.id}
                    </foreach>
                    then #{info.examUpDay}
                    end
                </if>
            </foreach>
        </trim>
        where pte.id in
        <foreach collection="infos" index="index" item="info" open="(" close=")" separator=",">
            #{info.id}
        </foreach>
    </update>

5.List集合中根据某些字段的值去重

public List<ProjectUserAttendance> deduplicate(List<ProjectUserAttendance> attendanceList) {
        return attendanceList.stream()
                .collect(Collectors.collectingAndThen(
                        Collectors.toMap(
                                record -> Arrays.asList(record.getUserId(), record.getProjectId(), record.getAttendDate(), record.getUserType()),
                                record -> record,
                                (existing, replacement) -> existing),
                        map -> new ArrayList<>(map.values())
                ));
    }

6. List集合两次分组成Map

Map<Long, List<Map<Long, ProjectUserInOutRecords>>> result = 
    projectUserInOutRecords.stream()
        .collect(Collectors.groupingBy(
            ProjectUserInOutRecords::getProjectId, 
            Collectors.mapping(
                record -> Collections.singletonMap(record.getProjectUserId(), record),
                Collectors.toList())
            ));

Map<Long, Map<Long, ProjectUserInOutRecords>> result = 
    projectUserInOutRecords.stream()
        .collect(Collectors.toMap(
            ProjectUserInOutRecords::getProjectId,
            record -> Collections.singletonMap(record.getProjectUserId(), record),
            (map1, map2) -> { // 合并函数，处理键冲突，默认直接使用第一个map
                if (map1 == null) return map2;
                if (map2 == null) return map1;
                // 这里简单地合并，如果有冲突，以第二个map的值为准，根据实际需求可能需要调整
                map1.putAll(map2);
                return map1;
            }));

7.使用LocalDate快速获取当月的所有日期

LocalDate parse = LocalDate.parse(attendDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        LocalDate startOfMonth = parse.with(TemporalAdjusters.firstDayOfMonth());
        LocalDate lastOfMonth = parse.with(TemporalAdjusters.lastDayOfMonth());

        List<LocalDate> dateList = new ArrayList<>();
        for (LocalDate date = startOfMonth; !date.isAfter(lastOfMonth); date = date.plusDays(1)) {
            dateList.add(date);
        }

8.使用SQL快速查询两个日期字段组成的周期中包含某个月份的数据

SELECT *
FROM your_table_name
WHERE 
    (date_colom_1 <= '月份的结束日期' AND (date_colom_2  IS NULL OR date_colom_2 >= '月份的起始日期'))

9.使用SQL查询identity_no字段相同，company_id不同的数据

SELECT
	identity_no,
	company_id 
FROM
	project_user 
GROUP BY
	identity_no,
	company_id 
HAVING
	COUNT(*) > 1 
ORDER BY
	identity_no,
	company_id;