截取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%'))