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

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



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


结果：
状态varchar系统varchar模块varchar零件varchar名称Varchar
Statussystemmodule hardwarename
故障'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
------解决方案-------------------- 
 
SQL code
if object_id('f_getname') is not null   drop function f_getnamegocreate function f_getname(@status varchar(4))returns varchar(10)asbegin   declare @s varchar(10),@system varchar(10),@module varchar(10),@hardware varchar(10)   select @system=system,@module=module,@hardware=hardware   from tb2    where status=@status   if exists(select 1 from tb where system=@system and module =@module and hardware=@hardware)      begin         select @s=name from tb where  system=@system and module =@module and hardware=@hardware      end   else if exists(select 1 from tb where system=@system and module =@module )        begin         select @s=name from tb where  system=@system and module =@module and hardware is null                 end        else if exists(select 1 from tb where system=@system )              begin                 select @s=name from tb where system=@system and module is null and hardware is null                              end              else              begin                select @s=name from tb where system is null and module is null and hardware is null                              end   return @send测试：select * ,name=dbo.f_getname(status) from tb2结果：故障    b    c    d    a运行    b    c    T    x低速    b    d    e    y过热    c    d    e    z
[解决办法]
太牵强了!
SQL code
create table ta(name varchar(10),system varchar(10),module varchar(10),hardware varchar(10))insert into ta select 'a','b','c','d' insert into ta select 'x','b','c',''insert into ta select 'y','b','',''insert into ta select 'z','','',''  create table tb(Status  varchar(10),system  varchar(10),module  varchar(10),hardware  varchar(10))insert into tb select '故障','b','c','d'insert into tb select '运行','b','c','T'insert into tb select '低速','b','d','e'insert into tb select '过热','c','d','e'goselect b.status,b.system,b.module,b.hardware,a.name from tb b inner join ta aon a.system=b.system and (a.module=b.module and (a.hardware=b.hardware or a.hardware='' and not exists(select 1 from ta where hardware=b.hardware))or a.module='' and not exists(select 1 from ta where module=b.module))or a.system='' and not exists(select 1 from ta where system=b.system)/*status     system     module     hardware   name---------- ---------- ---------- ---------- ----------故障         b          c          d          a运行         b          c          T          x低速         b          d          e          y过热         c          d          e          z(4 行受影响)*/godrop table ta,tb