如何编写SQL查询获取两表间匹配指定KEYCODE的table1记录

基础信息 #

现有两张主键互不关联的业务表：

• table1：共3个字段，分别是ID、NOM、CODE，样例数据共4条：
  • ID=1，NOM=AAA，CODE=661YYYDD
  • ID=2，NOM=BBB，CODE=YYYD661
  • ID=3，NOM=CCC，CODE=YD661
  • ID=4，NOM=DDD，CODE=P5500Z
• table2：共2个字段，分别是ID、KEYCODE，样例数据共2条：
  • ID=1，KEYCODE=661
  • ID=2，KEYCODE=55

需求为：根据KEYCODE值，筛选出table1中CODE字段包含对应KEYCODE子串的所有记录，例如筛选KEYCODE为661时，仅返回table1的前3条记录。

具体实现 #

单KEYCODE值查询 #

如果是指定单个KEYCODE做筛选，直接用模糊匹配查询即可，通用SQL写法：

SELECT * FROM table1
WHERE CODE LIKE CONCAT('%', 传入的KEYCODE值, '%');

以查询KEYCODE=661为例，执行上述SQL后，会自动匹配所有CODE字段包含661的记录，也就是样例中ID为1、2、3的3条数据，ID=4的CODE为P5500Z不包含661，不会被返回。

不同数据库的字符串拼接语法有细微差别：

• MySQL、PostgreSQL直接用上述CONCAT写法即可
• Oracle需要将拼接部分写为 '%' || 传入的KEYCODE值 || '%'
• SQL Server两种拼接写法都兼容

关联table2批量查询所有匹配结果 #

如果需要一次性查出table2中所有KEYCODE能匹配到的table1记录，可以用JOIN关联+模糊匹配条件实现：

SELECT DISTINCT t1.*
FROM table1 t1
JOIN table2 t2
ON t1.CODE LIKE CONCAT('%', t2.KEYCODE, '%');

语句中加DISTINCT是为了避免同一条table1记录同时匹配多个KEYCODE时出现重复结果，如果需要同步展示每条记录匹配到的KEYCODE值，可以将查询字段改为t1.*, t2.KEYCODE，此时可以去掉DISTINCT。

样例结果校验 #

针对给出的测试数据：

• 匹配KEYCODE=661：返回table1中ID=1、2、3的3条记录，符合预期
• 匹配KEYCODE=55：返回table1中ID=4的记录，因为其CODE值P5500Z包含55子串

内容的提问来源于stack exchange，提问作者Hamza Khadhri