如果x,y,z三个字段只存在于a表或b表,你是对的: select x,y,z from a left join b on a.id = b.id where b.name = 'c' group by x,y,z
--定义表a declare @a table (id int ,x int,y varchar(3),z varchar(3)) insert into @a select 1,1,'a','a' union all select 2,2,'b','b' union all select 3,3,'c','c'--定义表b declare @b table (id int,name varchar(15)) insert into @b select 1,'[email protected]' union all select 2,'c' union all select 3,'[email protected]'select x,y,z from @a a left join @b b on a.id = b.id where b.name = 'c' group by x,y,z/* x y z ----------- ---- ---- 2 b b */没有问题
关键要看你要结果集是什么样子的。b.name在where之后,相当于INNER JOIN了,where b.name = 'c'查询的结果集中只包含b.name = 'c'的结果了。这种情况下相当于inner join select x,y,z from a join b on a.id = b.id where b.name = 'c' group by x,y,z 或者 select x,y,z from a join b on a.id = b.id on b.name = 'c' group by x,y,z select x,y,z from a left join b on a.id = b.id and b.name = 'c' group by x,y,z 结果集中包含在a中有而b中没有的数据。
呵呵,也可以这样写: select x,y,z from a left join b on a.id = b.id and b.name = 'c' group by x,y,z
应该是这样的比较规范点吧:select x,y,z from a left join b on a.id = b.id and b.name = 'c' group by x,y,z on后面是左连接的条件,where后面应该是主表的条件
select x,y,z
from a
left join b
on a.id = b.id
where b.name = 'c'
group by x,y,z
--定义表a
declare @a table (id int ,x int,y varchar(3),z varchar(3))
insert into @a
select 1,1,'a','a' union all
select 2,2,'b','b' union all
select 3,3,'c','c'--定义表b
declare @b table (id int,name varchar(15))
insert into @b
select 1,'[email protected]' union all
select 2,'c' union all
select 3,'[email protected]'select x,y,z from @a a
left join @b b
on a.id = b.id
where b.name = 'c' group by x,y,z/*
x y z
----------- ---- ----
2 b b
*/没有问题
select x,y,z from a join b on a.id = b.id where b.name = 'c' group by x,y,z
或者
select x,y,z from a join b on a.id = b.id on b.name = 'c' group by x,y,z
select x,y,z from a left join b on a.id = b.id and b.name = 'c' group by x,y,z
结果集中包含在a中有而b中没有的数据。
select x,y,z
from a
left join b
on a.id = b.id and b.name = 'c'
group by x,y,z
on后面是左连接的条件,where后面应该是主表的条件