declare @t1 table(id varchar(10)) insert @t1 select 'a1' insert @t1 select 'a2' insert @t1 select 'a3' insert @t1 select 'a4' insert @t1 select 'a5'declare @t2 table(id varchar(10),name varchar(10)) insert @t2 select 'a1','张三' insert @t2 select 'a3','李四'select a.id,isnull(b.name,'未知名') from @t1 a left join @t2 b on a.id=b.id
declare @t1 table(id varchar(10)) insert @t1 select 'a1' insert @t1 select 'a2' insert @t1 select 'a3' insert @t1 select 'a4' insert @t1 select 'a5'declare @t2 table(id varchar(10),name varchar(10)) insert @t2 select 'a1','张三' insert @t2 select 'a3','李四'select a.id,isnull(b.name,'未知名')as name from @t1 a left join @t2 b on a.id=b.id id name ---------- ---------- a1 张三 a2 未知名 a3 李四 a4 未知名 a5 未知名(所影响的行数为 5 行)
create table aa(ID nvarchar(20)) insert into aa(ID) select 'a1' union select 'a2' union select 'a3' union select 'a4' union select 'a5'create table bb(ID nvarchar(20),name nvarchar(20)) insert into bb(ID,name) select 'a1','张三' union select 'a3','李四'select aa.ID,isnull(bb.name,'未知名') from aa left join bb on aa.ID = bb.IDdrop table aa drop table bb
select a.ID,isnull(b.name,'未知名') from aa a left join bb b on a.ID=b.ID
a1
a2
a3
a4
a5一表bbID namea1 张三
a3 李四需要得到如下结果应该怎么写查询语句呢?ID name
a1 张三
a2 未知名
a3 李四
a4 未知名
a5 未知名
insert @t1 select 'a1'
insert @t1 select 'a2'
insert @t1 select 'a3'
insert @t1 select 'a4'
insert @t1 select 'a5'declare @t2 table(id varchar(10),name varchar(10))
insert @t2 select 'a1','张三'
insert @t2 select 'a3','李四'select a.id,isnull(b.name,'未知名') from @t1 a left join @t2 b on a.id=b.id
insert @t1 select 'a1'
insert @t1 select 'a2'
insert @t1 select 'a3'
insert @t1 select 'a4'
insert @t1 select 'a5'declare @t2 table(id varchar(10),name varchar(10))
insert @t2 select 'a1','张三'
insert @t2 select 'a3','李四'select a.id,isnull(b.name,'未知名')as name from @t1 a left join @t2 b on a.id=b.id
id name
---------- ----------
a1 张三
a2 未知名
a3 李四
a4 未知名
a5 未知名(所影响的行数为 5 行)
insert into aa(ID)
select 'a1' union
select 'a2' union
select 'a3' union
select 'a4' union
select 'a5'create table bb(ID nvarchar(20),name nvarchar(20))
insert into bb(ID,name)
select 'a1','张三' union
select 'a3','李四'select aa.ID,isnull(bb.name,'未知名') from aa
left join bb on aa.ID = bb.IDdrop table aa
drop table bb