sql模糊关联匹配

需求目标：
在这里插入图片描述

建立临时表

drop table grafana_bi.zbj_gift_2024;USE grafana_bi;
CREATE  TABLE zbj_gift_2024 (id INT AUTO_INCREMENT PRIMARY KEY,userName VARCHAR(255),giftName VARCHAR(255),giftNum INT,points INT,teacher VARCHAR(255),sendDate DATETIME,terraceId INT,round_beginTime DATETIME,round_endTime DATETIME,round_teacher VARCHAR(255),ter INT
);

将查询数据插入临时表

-- V2.0 doubao AI ---------------------------------------------------------------------------INSERT INTO grafana_bi.zbj_gift_2024 (id, userName, giftName, giftNum, points, teacher, sendDate, terraceId, round_beginTime, round_endTime, round_teacher, ter)
SELECT NULL, t1.userName, t1.giftName, t1.giftNum, t1.points, t1.teacher, t1.sendDate, t1.terraceId, STR_TO_DATE(t2.beginTime, '%Y-%m-%d %H:%i:%s') beginTime, STR_TO_DATE(t2.endTime, '%Y-%m-%d %H:%i:%s') endTime, t2.round_teacher, t2.ter
FROM 
(-- 用户打赏日志表SELECT *FROM zbj_giftWHERE --          date(sendDate) = '2024-12-30'date(sendDate) >= '2024-01-01' AND date(sendDate) <= '2024-12-31'AND terraceId IN(1, 3)
) t1
LEFT JOIN
(SELECT  CONCAT(round_date,' ',round_beginTime) beginTime,CONCAT(round_date,' ',CASE WHEN round_beginTime > round_endTime THEN '23:59:59'ELSE round_endTime END) endTime,round_teacher,terraceId AS terFROM zbj_roundWHERE date(round_date) >= '2024-01-01' AND date(round_date) <= '2024-12-31'AND terraceId IN(1, 3)
) t2
ON (t1.sendDate >= t2.beginTime AND t1.sendDate <= t2.endTime --    and t1.teacher= t2.round_teacher   AND t2.round_teacher LIKE CONCAT('%', t1.teacher, '%') AND t1.terraceId = t2.ter
)
order by terraceId,sendDate;

难点&方法论沉淀

-- 【测试】检查 '浩柠老师/主播依然' 是否包含 '浩柠老师'SELECT '浩柠老师/主播依然' LIKE CONCAT('%','浩柠老师', '%');-- 应用t2.round_teacher LIKE CONCAT('%', t1.teacher, '%')t2.round_teacher REGEXP t1.teacher