create table A(name varchar(3), system varchar(3), module varchar(3), hardware varchar(3))
insert a
select 'a' ,'b' ,'c' ,'d' union all
select 'x' ,'b' ,'c' ,'' union all
select 'y' ,'b' ,'' ,'' union all
select 'z' ,'' ,'' ,''   

create table  B(Status varchar(8), system varchar(3), module varchar(3),  hardware varchar(3))
insert b
select '故障', 'b', 'c', 'd' union all
select '运行', 'b', 'c', 'T' union all
select '低速', 'b', 'd', 'e' union all
select '过热', 'c', 'd', 'e'


select * into #a from a
select * into #b from b

select #b.*,#a.name into #c
from #b ,#a
where  #a.system=#b.system 
and    #a.module=#b.module 
and    #a.hardware=#b.hardware

delete from #a 
from #c
where  #a.system=#c.system and #a.module=#c.module and  #a.hardware=#c.hardware

delete from #b 
from #c
where  #b.system=#c.system and #b.module=#c.module and  #b.hardware=#c.hardware

insert #c select #b.*,#a.name 
from #b ,#a
where  #a.system=#b.system 
and    #a.module=#b.module 

delete from #a 
from #c
where  #a.system=#c.system and #a.module=#c.module 
delete from #b 
from #c
where  #b.system=#c.system and #b.module=#c.module 


insert #c select #b.*,#a.name 
from #b ,#a
where  #a.system=#b.system 

delete from #a 
from #c
where  #a.system=#c.system 
delete from #b 
from #c
where  #b.system=#c.system 

insert #c select #b.*,#a.name 
from #b ,#a

select * from #c

drop table b,a,#a,#b,#c
/*
Status   system module hardware name 
-------- ------ ------ -------- ---- 
运行       b      c      T        x
低速       b      d      e        y
过热       c      d      e        z
故障       b      c      d        a
*/

------解决方案--------------------
有意思吗？还是从修改表结构上下功夫吧，不然以后会越来越难受的

------解决方案--------------------
探讨

嘿嘿，各位见笑了，这是从项目中提取出来的，我用这个例子说明。
由于2个表不能建立主外键关系，且又存在这样的分级匹配关系，实在想不出别的好办法。


------解决方案--------------------

SQL code

--try
select *,name=(select top 1 name from (
                select name from A表 where system=a.system union all
                select name from A表 where module=a.module union all
                select name from A表 where hardware=a.hardware union all) t 
                group by name order by count(1) desc) 
from B表 a

------解决方案--------------------