1 前言
朋友最近遇到一个比较棘手的 sql 问题，让我帮忙看看：
他有两张表 testa 和 testb ，一个表存的日期，另一个表存字符串例如 2023-11-01,2023-11-02，如何将这两张表关联起来，只查 testa 表的数据（关联的时候，其中只要在 testb 表中的字符串有这个日期，就要将 testa 表是这个日期的记录的所有字段都查出来）。
我当时看的时候，觉得这个还挺简单的，想着用 in 或者 like 就行了，后发现都不是正解，于是便有了这篇文章产生。
2 建表和插入测试数据
## testa 表 （每个日期一条数据，日期的格式含秒）
CREATE TABLE `testa` (
`operator_id` int NOT NULL,
`stat_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
## testb 表（多个日期一条数据，日期以英文字符分隔，日期是 YYYY-MM-DD 形式）
CREATE TABLE `testb` (
`operator_id` int NOT NULL,
`rectify_date` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-03 00:00:00');
INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-04 00:00:00');
INSERT INTO `testa` (`operator_id`, `stat_date`) VALUES (11, '2024-01-02 00:00:00');
INSERT INTO testb (`operator_id`, `rectify_date`) VALUES (11, '2024-01-02,2024-01-04');
3 查询语句
## rectify_date为需要拆分的字段
select a.operator_id, a.stat_date from testa a ,
( SELECT @rownum := @rownum + 1 as rownum ,
t2.operator_id as operator_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(t2.rectify_date, ',', t3.help_topic_id + 1), ',', - 1) as rectify_date
FROM (SELECT @rownum := 0) t1 , testb t2
JOIN mysql.help_topic t3 ON t3.help_topic_id < (LENGTH(t2.rectify_date) - LENGTH(REPLACE(t2.rectify_date, ',', '')) + 1)
) b
where SUBSTR(a.stat_date,1,10) = b.rectify_date
4 涉及知识点
4.1 @rownum
自定义变量，通过赋值语句 @rownum:=@rownum + 1 来累加达到递增行号的需求。
（ @rownum 是自定义变量而不是 Mysql 的函数，所以名字是可以随便取的比如 @rowNo @aaa @bbb 皆可。）
4.2 help_topic
系统自带的辅助表，mysql.help_topic 表的 id 特点是从 0 开始递增，最大为 681
详见《mysql.help_topic 数量/最大值上限查询》
4.3 SUBSTRING
字符串截取，SUBSTRING(str,pos,len)
- str 要截取的字符串
- pos 开始截取字符串的下标位置
- len 需要截多长
4.4 SUBSTRING_INDEX
切割字符串，SUBSTRING_INDEX(str,delim,count)
- str 表示要进行分割的字符串
- delim 表示分割符
- count表示要返回的子串的个数
5 总结
- 总体上来说，还是挺简单的，主要是依靠一张辅助表，将一行有规律的数据拆分成多行。然后再配合一些常用函数来做切割；
- 也可以考虑放在 java 代码中的 for 循环处理实现，不过数据量较大的情况下，会很吃 java 内存。java 和 数据库两个层面处理，各有利弊，具体看哪块的资源比较多，自行参考即可；
