多表查询新手跪求大侠解决思路_Oracle面试

多表查询新手跪求大侠
四张表
A表字段有 id name number
B表字段有 name user
C表字段有 name user
D表字段有 id username

如果我想显示 A表的number B或C中的user D中的username
如果A中的name与B中的name相等显示B中的user，与C中的name相等显示C表的user
怎样写SQL语句?

跪求大侠！！！！

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

SQL code

select   a.number, bc.user, d.usernamefrom  a left join  (select name, user from b union all   select name, user from c) bc on a.name = bc.name left join  d on a.id = d.id
------解决方案--------------------------------------------------------
 select a.number,bc.user,d.username
  from a,d,(select name,user from b union select name,user from c)bc
  where a.name=bc.name
  and   a.id=d.id

------解决方案--------------------------------------------------------
union关联了。
------解决方案--------------------------------------------------------
SQL codewith ta as(     select '1001' id,'第一号机器' name,10 num from dual union all     select '1002','第二号机器',20 from dual union all     select '1003','第三号机器',30 from dual union all     select '1004','第四号机器',40 from dual union all     select '1005','第五号机器',50 from dual),tb as(    select '第一号机器' name,'Operator' users from dual union all    select '第四号机器','Manager' from dual union all    select '第三号机器','Manipulator' from dual),td as(    select '1001' id,'James' user_name from dual union all    select '1002','Smith' from dual union all    select '1003','Scott' from dual union all    select '1004','Jim' from dual)select ta.id,ta.name,tb.users,td.user_namefrom ta,tb,tdwhere ta.id=td.id --使用等值连接即可  and ta.name=tb.name/ID   NAME       USERS       USER_NAME---- ---------- ----------- ---------1001 第一号机器 Operator    James1003 第三号机器 Manipulator Scott1004 第四号机器 Manager     Jim

多表查询 新手跪求大侠解决思路

多表查询新手跪求大侠解决思路