--try select a.id,name,city,sex from 表 a left join 表 b on a.id=b.id where name='abc'
select a.id ,a.name ,b.city ,b.sex from a left join b on a.id=b.id where a.name='abc'
谢谢。不好意思,如果数据增加个条件,又该如何?表 a: id name 1 abc 2 fh 3 kl 4 abc 5 abc表 b: id city sex 1 北京 m 2 北京 f 3 上海 m 5 广州 f条件:name=abc,sex=m或数据不存在 结果: id name city sex 1 abc 北京 m 4 abc null null
--测试环境 declare @表a table(id int, name varchar(10)) insert into @表a select 1,'abc' union all select 2,'fh' union all select 3,'k1' union all select 4,'abc'declare @表b table(id int,city varchar(10),sex varchar(10)) insert into @表b select 1,'北京','m' union all select 2,'北京','f' union all select 3,'上海','m' --左连接查询 select a.id, name, city, sex from (select id,name from @表a where name='abc') a left join @表b b on a.id=b.id --结果id name city sex ----------- ---------- ---------- ---------- 1 abc 北京 m 4 abc NULL NULL(所影响的行数为 2 行)
--再加"sex"条件如下: select a.id, name, city, sex from (select id,name from @表a where name='abc') a left join (select id,city,sex from @表b where sex='m') b on a.id=b.id--结果 id name city sex ----------- ---------- ---------- ---------- 1 abc 北京 m 4 abc NULL NULL(所影响的行数为 2 行)
select a.id,name,city,sex
from 表 a left join 表 b on a.id=b.id
where name='abc'
,a.name
,b.city
,b.sex
from a
left join b on a.id=b.id
where a.name='abc'
id name
1 abc
2 fh
3 kl
4 abc
5 abc表 b:
id city sex
1 北京 m
2 北京 f
3 上海 m
5 广州 f条件:name=abc,sex=m或数据不存在 结果:
id name city sex
1 abc 北京 m
4 abc null null
declare @表a table(id int, name varchar(10))
insert into @表a select 1,'abc'
union all select 2,'fh'
union all select 3,'k1'
union all select 4,'abc'declare @表b table(id int,city varchar(10),sex varchar(10))
insert into @表b select 1,'北京','m'
union all select 2,'北京','f'
union all select 3,'上海','m'
--左连接查询
select
a.id,
name,
city,
sex
from (select id,name from @表a where name='abc') a left join @表b b on a.id=b.id
--结果id name city sex
----------- ---------- ---------- ----------
1 abc 北京 m
4 abc NULL NULL(所影响的行数为 2 行)
select
a.id,
name,
city,
sex
from (select id,name from @表a where name='abc') a left join (select id,city,sex from @表b where sex='m') b on a.id=b.id--结果
id name city sex
----------- ---------- ---------- ----------
1 abc 北京 m
4 abc NULL NULL(所影响的行数为 2 行)