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_dateFROM (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 和 数据库两个层面处理,各有利弊,具体看哪块的资源比较多,自行参考即可;