一道面试题看图写sql语句

[None726]

求助大神写出sql

[CT_LXL]

引用 楼主 l1405056211 的回复:

求助大神写出sql

select t1.id,
       nvl((select t2.name from user t2 where t1.userid1=t2.id),'未指定') username1,
       nvl((select t3.name from user t3 where t1.userid2=t3.id),'未指定') username2
from a t1

[None726]

可以详细说明吗

[CT_LXL]

引用 2 楼 l1405056211 的回复:

可以详细说明吗

每个ID加一个子查询，子查询没有值，就用NVL函数指定为‘未指定’。

[myue]

if object_id('a') is not null drop table a
create table a (id varchar (5),uid1 varchar (5),uid2 varchar (5) )
insert  into  a (id,uid1,uid2)
select 0,0,NULL union all 
select 1,NULL,1 union all 
select 2,2,1 
if object_id('usr') is not null drop table usr
create table usr (id varchar (5),nm varchar (5) )
insert  into  usr (id,nm)
select 0,'zh' union all 
select 1,'li' union all 
select 2,'w'
select a.id,
	(case when uid1=usr.id then usr.nm else NULL end  )as nm1 ,
	(case when uid2=usr.id then usr.nm else NULL end  )as nm2  
from a join usr on a.id=usr.id

[大脸猫o]

select id, (select nvl(b.name,'未指定') from b where b.id = a.userid1) username1, (select nvl(b.name,'未指定') from b where b.id = a.userid2) username2 from a 你可以试一下

[king5645608]

引用 4 楼 Myue 的回复:

if object_id('a') is not null drop table a
create table a (id varchar (5),uid1 varchar (5),uid2 varchar (5) )
insert  into  a (id,uid1,uid2)
select 0,0,NULL union all 
select 1,NULL,1 union all 
select 2,2,1 
if object_id('usr') is not null drop table usr
create table usr (id varchar (5),nm varchar (5) )
insert  into  usr (id,nm)
select 0,'zh' union all 
select 1,'li' union all 
select 2,'w'
select a.id,
	(case when uid1=usr.id then usr.nm else NULL end  )as nm1 ,
	(case when uid2=usr.id then usr.nm else NULL end  )as nm2  
from a join usr on a.id=usr.id

有必要写这么多么^_^

[WSZHAO_SELECT]

select a.id,
    nvl((select name from t_user where id=a.userid1),'未指定') username1,
      nvl((select name from t_user where id=a.userid2),'未指定') username2
      from A a;

和上面一个重复了！！

[IWE]

select id, (select nvl(b.name,'未指定') from b where b.id = a.userid1) username1, (select nvl(b.name,'未指定') from b where b.id = a.userid2) username2 from a

[小灰狼W]

考你外连接呢。这道题做不出来，考试悬了哈

[请叫我-雷人]

能看懂图就能写出来吧！嘿嘿

[物贵梦绕]

这个 我面试的时候写过 写出来了 但是没有被录用 我当时是这样做的 select aa1.id,username1 name1,username2 name2 from ( (select a.id,name username1 from (a left join user_a on a.userid1=user_a.id)) aa1 join (select a.id,name username2 from (a left join user_a on a.userid2=user_a.id)) aa2 on aa1.id=aa2.id);

[luoyanqun]

哈哈，很多种方法实现，我这边用case when 实现 --CASE WHEN 实现 SELECT A.ID, CASE WHEN A.USERID1 = B.ID THEN B.NAME ELSE NULL END, CASE WHEN A.USERID2 = B.ID THEN B.NAME ELSE NULL END USER1 FROM A A, USER1 B