数据同步中不可避免的就是一大串的sql语句,常用的group by、 order by asc desc、limit、bettwen and、like ‘%x’、in、not in 、is null、is not null 、left join on、join on、right join on已经不够用了。因此记录一下,稍微复杂一点的sql语句。

1. join匹配多条只选一条

select
	*
from
	d
left join (
	select
		*,
		row_number() over (partition by a1
	order by
		field(a2,'3','2')) as rn FROM c) t2 on d.a1=t2.a1
WHERE 
    t2.rn = 1;

2.join 一对多会发生什么

不管使用哪种连接方式,只要存在1对多,那么得到的查询结果都会比主表记录数多

3. field 自定义排序

SELECT * 
FROM products
ORDER BY FIELD(category, 'Clothing', 'Home', 'Electronics');

4.case when end as条件语句

SELECT 
    a1, 
    c1, 
    c2, 
    c3,
    CASE 
	when c1 is null and c2 is null then -c3
        WHEN c1 < c2 and c1 is not null or c2 is null THEN c1 - c3 
        WHEN c1 >= c2 and c2 is not null or c1 is null THEN c2 - c3 
    END AS result
FROM B;

5.获取当月首日和末日0点

select
	STR_TO_DATE(DATE_FORMAT(last_day(current_date()), '%Y-%m-01 00:00:00'),
	'%Y-%m-%d %H:%i:%s') as first_day_of_month;
select
	STR_TO_DATE(DATE_FORMAT(last_day(current_date()), '%Y-%m-%d 00:00:00'),
	'%Y-%m-%d %H:%i:%s') as last_day_of_month;
-- 获取前三个月首日
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01') AS first_day_of_three_months_ago;

6.having获取重复数据并计数

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

7.ifnull的高效之处

判断是否在有效期内,而失效日期可能为null时。
使用BETWEEN cr.EFF_DT AND IFNULL(cr.EXP_DT, STR_TO_DATE('20991231', '%Y%m%d')):
比BETWEEN cr.EFF_DT AND cr.EXP_DT OR cr.EXP_DT IS NULL:高效


BETWEEN cr.EFF_DT AND IFNULL(cr.EXP_DT, STR_TO_DATE('20991231', '%Y%m%d')):

这个条件首先检查 cr.EFF_DT 和 cr.EXP_DT 之间的范围。如果 cr.EXP_DT 是 NULL,则使用一个固定的日期(2099-12-31)作为替代。这使得查询可以在一次比较中处理有效日期和 NULL 的情况。
因为这个条件总是会产生一个有效的范围(即使是用2099年作为替代),数据库引擎可以更有效地使用索引来查找符合条件的记录。
BETWEEN cr.EFF_DT AND cr.EXP_DT OR cr.EXP_DT IS NULL:

这个条件则需要分开处理两种情况:一种是 cr.EXP_DT 不为 NULL 的情况,另一种是 cr.EXP_DT 为 NULL 的情况。
这种写法需要数据库引擎在执行时进行更多的逻辑判断,这可能导致更复杂的查询计划和较低的执行效率,特别是在数据量大时。
对于每一行,数据库都必须评估 OR 条件,因此可能会导致更多的行被扫描,尤其是当 EXP_DT 的 NULL 值占比较高时。
总结来说,使用 IFNULL 将 NULL 值替换为一个固定日期的策略可以简化查询逻辑,使得数据库引擎在执行时能够更高效地利用索引和优化查询计划,从而提高执行效率。

8.group_concat(distinct cr.rank_cd) 去重+字符串连接

group_concat(distinct cr.rank_cd): 这个函数用于将 cr.rank_cd 字段的不同值(去重后)合并成一个字符串,默认用逗号 , 作为分隔符。也就是说,如果 cr.rank_cd 的值有多个不同的项,group_concat 会把这些项连接成一个以逗号分隔的字符串。

9.replace(…, ‘,S/S’, ”) 替换

replace(..., ',S/S', ''): 这是一个字符串替换操作,目的是将上一步生成的字符串中所有出现的 ,S/S 这个子串替换为空字符串 ''。也就是说,所有 ,S/S 的部分在最终的结果中将被移除。

10.group_concat+replace 多条记录时去除指定字符串,单条时保留

replace (replace(group_concat(distinct cr.rank_cd),',S/S','') ,'S/S,','')as rank_cd,: 这个替换操作与上一个类似,它将字符串中所有出现的,S/S和 S/S, 的部分也替换为空字符串 ''。这样,如果 S/S 出现在字符串的开头或其它位置,它也会被移除。如果只是单个则不会被移除。

11.sum统计xx出现次数

sum(case when cp.PASSPORT_TYPE = 'OTHR' then 1 else 0 end)  as OTHR_CNT

12.按月汇总

-- mysql
SELECT 
    str_to_date(DATE_FORMAT(duty_date, '%Y%m01'),'%Y%m%d') AS month,
    employee_id,
    SUM(work_hours) AS total_work_hours
FROM 
    DutySchedule
    WHERE 
    duty_date>=DATE(CONCAT(YEAR(CURDATE()), '-01-01'))
GROUP BY 
    month, employee_id;
   
-- oracle
SELECT 
    TRUNC(duty_date, 'MM') AS month,
    employee_id,
    SUM(work_hours) AS total_work_hours
FROM 
    DutySchedule
WHERE 
    EXTRACT(YEAR FROM duty_date) = EXTRACT(YEAR FROM SYSDATE)
GROUP BY 
    TRUNC(duty_date, 'MM'), employee_id;  

13.公共表表达式 (CTE)

  1. 公共表表达式 (CTE): 使用 WITH 语句将三个不同的查询合并为一个名为 MonthlyWorkHours 的 CTE,这样可以减少重复代码。
  2. 交叉连接生成索引: 使用 CROSS JOIN 生成一个包含 0 到 3 的索引的临时表,这样可以在一个查询中处理所有不同的月份。
  3. 简化主查询: 在最终的 SELECT 语句中,简化了对不同月份的查询,便于管理和理解。

这样处理后,代码更清晰,也更容易维护。

  WITH MonthlyWorkHours AS (
    SELECT 
    	idx,
        e.employee_id,
        e.department,
        e.position,
        DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL idx MONTH), '%Y-%m') AS month,
        SUM(ws.work_hours) AS total_work_hours,
        hq.limit_duration AS hq_limit_duration,
        pd.limit_duration AS pd_limit_duration
    FROM 
        (SELECT employee_id, department, position FROM employees) e
    LEFT JOIN 
        DutySchedule ws ON e.employee_id = ws.employee_id 
    LEFT JOIN 
        HQLimitDuration hq ON e.department = hq.department AND e.position = hq.position 
    LEFT JOIN 
        PersonalLimitDuration pd ON e.employee_id = pd.employee_id 
        AND CURRENT_DATE BETWEEN pd.start_date AND pd.end_date
    CROSS JOIN (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS idx -- 生成 0-3 的索引
    WHERE 
        (idx = 0 AND ws.duty_date >= DATE_FORMAT(CURDATE(), '%Y-01-01'))
        OR (idx = 1 AND ws.duty_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01'))
        OR (idx = 2 AND ws.duty_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01'))
        OR (idx = 3 AND ws.duty_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m-01'))
    GROUP BY idx,
           e.employee_id,
        e.department,
        e.position, hq.limit_duration,
        pd.limit_duration,month
)

SELECT 
    g1.month,
    g1.employee_id,
    CASE 
        WHEN g1.remaining_work_hours is NOT NULL or g2.remaining_work_hours IS NOT NULL or g3.remaining_work_hours IS NOT NULL THEN 
            LEAST(IFNULL(g1.remaining_work_hours, 99999999), IFNULL(g2.remaining_work_hours, 99999999), IFNULL(g3.remaining_work_hours, 99999999))
        ELSE 
            NULL 
    END AS remaining_work_hours
FROM 
    (SELECT 
        month,
        employee_id,
        LEAST(IFNULL(hq_limit_duration, 0), IFNULL(pd_limit_duration, 0)) - IFNULL(total_work_hours, 0) AS remaining_work_hours
     FROM 
        MonthlyWorkHours
     WHERE idx = 0) g1
left JOIN 
    (SELECT 
        month,
        employee_id,
        LEAST(IFNULL(hq_limit_duration, 0), IFNULL(pd_limit_duration, 0)) - IFNULL(total_work_hours, 0) AS remaining_work_hours
     FROM 
        MonthlyWorkHours
     WHERE idx = 1) g2 ON g1.employee_id = g2.employee_id AND g1.month = g2.month
left JOIN 
    (SELECT 
        month,
        employee_id,
        LEAST(IFNULL(hq_limit_duration, 0), IFNULL(pd_limit_duration, 0)) - IFNULL(total_work_hours, 0) AS remaining_work_hours
     FROM 
        MonthlyWorkHours
     WHERE idx = 3) g3 ON g1.employee_id = g3.employee_id AND g1.month = g3.month
ORDER BY 
    g1.month;