select a.*,b.sname from 数据表1 a left join 数据表2 on a.pid= b.pid
select a.pid,a.pname,b.sname from 数据表1 a left join 数据表2 b on a.pid = b.pid
--建立测试环境 Create Table 表(pid varchar(10),pname varchar(10)) Create Table 表2(sid varchar(10),pid varchar(10),sname varchar(10)) --插入数据 insert into 表 select '1','aaa' union select '2','bbb' union select '3','ccc' union select '4','ddd' union select '5','eee' union select '6','fff' select * from 表insert into 表2 select '1','3','ggg' union select '2','4','hhh' union select '3','5','iii' select * from 表2 --测试语句 select a.pid, a.pname, b.sname from 表 a left join 表2 b on a.pid = b.pid
--删除测试环境 Drop Table 表 Drop Table 表2--结果 1 aaa NULL 2 bbb NULL 3 ccc ggg 4 ddd hhh 5 eee iii 6 fff NULL
Select A.pid,A.pname,B.sname from 数据表1 A Left Join 数据表2 B On A.pid=B.pid And B.sid=2
--创建测试环境 Create table 表1 ( pid int identity(1,1),pname varchar(10)) insert into 表1 select 'aaa' union all select 'bbb' union all select 'ccc' union all select 'ddd' union all select 'eee' union all select 'fff'Create table 表2 ( sid int identity(1,1),pid int,sname varchar(10)) insert into 表2 select 3,'ggg' union all select 4, 'hhh' union all select 5,'iii'--查询 select 表1.pid,pname,sname=case when(表2.sid=2) then sname else null end from 表1 left join 表2 on 表1.pid=表2.pid --结果 pid pname sname ----------- ---------- ---------- 1 aaa NULL 2 bbb NULL 3 ccc NULL 4 ddd hhh 5 eee NULL 6 fff NULL(所影响的行数为 6 行) --删除测试环境 drop table 表1 drop table 表2
Select A.pid,A.pname,B.sname from 数据表1 A Left Join 数据表2 B On A.pid=B.pid And B.sid=2
a.pid,a.pname,b.sname
from
数据表1 a
left join
数据表2 b
on
a.pid = b.pid
Create Table 表(pid varchar(10),pname varchar(10))
Create Table 表2(sid varchar(10),pid varchar(10),sname varchar(10))
--插入数据
insert into 表
select '1','aaa' union
select '2','bbb' union
select '3','ccc' union
select '4','ddd' union
select '5','eee' union
select '6','fff'
select * from 表insert into 表2
select '1','3','ggg' union
select '2','4','hhh' union
select '3','5','iii'
select * from 表2
--测试语句
select a.pid, a.pname, b.sname
from 表 a left join 表2 b
on a.pid = b.pid
--删除测试环境
Drop Table 表
Drop Table 表2--结果
1 aaa NULL
2 bbb NULL
3 ccc ggg
4 ddd hhh
5 eee iii
6 fff NULL
pid pname sname
-----------------
1 aaa null
2 bbb null
3 ccc null
4 ddd hhh //就是只选择数据表2中sid=2的记录
5 eee null
6 fff null
from 数据表1 A
Left Join 数据表2 B
On A.pid=B.pid
And B.sid=2
Create table 表1 ( pid int identity(1,1),pname varchar(10))
insert into 表1 select 'aaa'
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
union all select 'eee'
union all select 'fff'Create table 表2 ( sid int identity(1,1),pid int,sname varchar(10))
insert into 表2 select 3,'ggg'
union all select 4, 'hhh'
union all select 5,'iii'--查询
select 表1.pid,pname,sname=case when(表2.sid=2) then sname else null end
from 表1 left join 表2 on 表1.pid=表2.pid
--结果
pid pname sname
----------- ---------- ----------
1 aaa NULL
2 bbb NULL
3 ccc NULL
4 ddd hhh
5 eee NULL
6 fff NULL(所影响的行数为 6 行)
--删除测试环境
drop table 表1
drop table 表2
from 数据表1 A
Left Join 数据表2 B
On A.pid=B.pid
And B.sid=2