求教SQL语句，具体见帖子内容，多谢_Sql Server

求教SQL语句，具体见帖子内容，谢谢。
A表:
名称varchar 系统varchar 模块varchar 零件varchar
name system module hardware
'a' 'b' 'c' 'd'
'x' 'b' 'c' ''
'y' 'b' '' ''
'z' '' '' ''

B表:
状态varchar 系统varchar 模块varchar 零件varchar
Status system module hardware
故障 'b' 'c' 'd'
运行 'b' 'c' 'T'
低速 'b' 'd' 'e'
过热 'c' 'd' 'e'

结果：
状态varchar 系统varchar 模块varchar 零件varchar 名称Varchar
Status system module hardware name
故障 'b' 'c' 'd' 'a'
运行 'b' 'c' 'T' 'x'
低速 'b' 'd' 'e' 'y'
过热 'c' 'd' 'e' 'z'

B表中的system/module/hardware如果在A表中有完全匹配的记录就用A表中该记录的name

如果B表中的system/module/hardware在A表中没有完全匹配的记录那么就在A表中找
system/module匹配且hardware为空的记录。

如果还没有就找system在A表中有匹配module/hardware都为空的记录。

如果还没有就找A表中system/module/hardware都为空的记录来匹配。

------解决方案--------------------
缺少条件.
------解决方案--------------------
这句话(以后全是):
如果B表中的system/module/hardware在A表中没有完全匹配的记录那么就在A表中找
system/module匹配且hardware为空的记录。
没有指明,在A表中找system/module匹配且hardware为空的记录时,B表中以什么来匹配.实际上,B表能匹配的有两个,分别是hardware为 d 和 T 的记录,为什么只匹配 T 而不匹配 d 呢?
------解决方案--------------------
这个表结构设计得。。。
------解决方案--------------------

SQL code

create table A(name varchar(3), system varchar(3), module varchar(3), hardware varchar(3))insert aselect 'a' ,'b' ,'c' ,'d' union allselect 'x' ,'b' ,'c' ,'' union allselect 'y' ,'b' ,'' ,'' union allselect 'z' ,'' ,'' ,''   create table  B(Status varchar(8), system varchar(3), module varchar(3),  hardware varchar(3))insert bselect '故障', 'b', 'c', 'd' union allselect '运行', 'b', 'c', 'T' union allselect '低速', 'b', 'd', 'e' union allselect '过热', 'c', 'd', 'e'select * into #a from aselect * into #b from bselect #b.*,#a.name into #cfrom #b ,#awhere  #a.system=#b.system and    #a.module=#b.module and    #a.hardware=#b.hardwaredelete from #a from #cwhere  #a.system=#c.system and #a.module=#c.module and  #a.hardware=#c.hardwaredelete from #b from #cwhere  #b.system=#c.system and #b.module=#c.module and  #b.hardware=#c.hardwareinsert #c select #b.*,#a.name from #b ,#awhere  #a.system=#b.system and    #a.module=#b.module delete from #a from #cwhere  #a.system=#c.system and #a.module=#c.module delete from #b from #cwhere  #b.system=#c.system and #b.module=#c.module insert #c select #b.*,#a.name from #b ,#awhere  #a.system=#b.system delete from #a from #cwhere  #a.system=#c.system delete from #b from #cwhere  #b.system=#c.system insert #c select #b.*,#a.name from #b ,#aselect * from #cdrop 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--tryselect *,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
------解决方案--------------------