数据同步中不可避免的就是一大串的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)
- 公共表表达式 (CTE): 使用
WITH
语句将三个不同的查询合并为一个名为MonthlyWorkHours
的 CTE,这样可以减少重复代码。 - 交叉连接生成索引: 使用
CROSS JOIN
生成一个包含 0 到 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;
发表评论