sql查找一列中包含另一张表截取部分字段的数据

数据库查询优化与数据匹配

最新推荐文章于 2024-07-22 03:05:39 发布

原创最新推荐文章于 2024-07-22 03:05:39 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #database

sql 专栏收录该内容

4 篇文章

订阅专栏

该博客主要涉及数据库查询优化，通过SQL语句示例展示了如何使用LIKE运算符和SUBSTRING函数进行数据匹配。讨论了如何在特定时间范围内筛选数据，并强调了Fixture_no列的前11个字符在连接不同表中的关键作用。

截取d表中Fixture_no列前11个字段，连接b表fixture_no列：
d.Fixture_no like concat(’%’,b.fixture_no,’%’)
or
b.fixture_no= substring(d.Fixture_no,1,11)

select a.part_num, a.mc_code, a.pm_no, a.NC_For, a.part_rev, b.RF, b.part_no, b.orientation, b.rev, b.fixture_no, c.fixture_no, c.Location, d.Fixture_no, d.Status, d.Origin, d.Trans_Date 
from [dbo].[partmachtime] as a,
[dbo].[fms_where_use] as b,
[dbo].[fms_store] as c,
[dbo].[fms_Trans_Record] as d
where a.part_num = b.part_no and a.NC_For = b.orientation and a.part_rev = b.rev and a.pm_no = d.Origin and a.pm_no= c.Location and b.RF = 'M'and Trans_Date between '2021-12-06 00:00:00.000' and '2021-12-12 23:59:59.000'
and d.Fixture_no like concat('%',b.fixture_no,'%')

select distinct b.part_no,d.Fixture_no  
from [dbo].[fms_Trans_Record] d,
[dbo].[fms_where_use] b
where b.fixture_no= SUBSTRING(d.Fixture_no,1,11) 
and d.Trans_Date between '2021-12-06 00:00:00.000' and '2021-12-12 23:59:59.000'AND d.Workshop='LMC'
and ((d.Origin like 'M%' and d.Destination like 'M%') or (d.Origin not like 'M%' and d.Destination not like 'M%'))