select a.city_id, city_name,b.object from a left join b on a.city_id=b.city_id
select a.city_id,city_name=a.name ,b.* from 表a a left join 表b b on a.city_id=b.city_id where 月份='2003-10'
--上面写错了字段名 select a.city_id,city_name=a.name ,b.* from 表a a left join 表b b on a.city_id=b.city_id where b.l_date='2003-10'
--上面还有错,应该是:select a.city_id,city_name=a.name ,b.* from 表a a left join 表b b on a.city_id=b.city_id where b.l_date='2003-10' or b.b.l_date is null
--下面是数据测试--测试表 declare @a table(city_id int,name varchar(10)) insert into @a select 1,'北京' union all select 2,'上海' union all select 3,'广州'declare @b table(id int,city_id int,object int,l_date varchar(7)) insert into @b select 1,1,800,'2003-10' union all select 2,1,1000,'2003-11' union all select 3,1,2000,'2003-12' union all select 4,2,100,'2003-10' union all select 5,2,500,'2003-8'--查询2003-10的数据 select a.city_id,city_name=a.name ,b.* from @a a left join @b b on a.city_id=b.city_id where b.l_date='2003-10' or b.l_date is null/*--测试结果 city_id city_name id city_id object l_date ----------- ---------- ----------- ----------- ----------- ------- 1 北京 1 1 800 2003-10 2 上海 4 2 100 2003-10 3 广州 NULL NULL NULL NULL(所影响的行数为 3 行) --*/
select a.city_id,city_name=a.name ,b.object,b_l_date from tableA a left join tabkeB b on a.city_id=b.city_id where b.l_date=condition
select a.city_id,city_name=a.name
,b.*
from 表a a left join 表b b on a.city_id=b.city_id
where 月份='2003-10'
select a.city_id,city_name=a.name
,b.*
from 表a a left join 表b b on a.city_id=b.city_id
where b.l_date='2003-10'
,b.*
from 表a a left join 表b b on a.city_id=b.city_id
where b.l_date='2003-10' or b.b.l_date is null
declare @a table(city_id int,name varchar(10))
insert into @a
select 1,'北京'
union all select 2,'上海'
union all select 3,'广州'declare @b table(id int,city_id int,object int,l_date varchar(7))
insert into @b
select 1,1,800,'2003-10'
union all select 2,1,1000,'2003-11'
union all select 3,1,2000,'2003-12'
union all select 4,2,100,'2003-10'
union all select 5,2,500,'2003-8'--查询2003-10的数据
select a.city_id,city_name=a.name
,b.*
from @a a left join @b b on a.city_id=b.city_id
where b.l_date='2003-10' or b.l_date is null/*--测试结果
city_id city_name id city_id object l_date
----------- ---------- ----------- ----------- ----------- -------
1 北京 1 1 800 2003-10
2 上海 4 2 100 2003-10
3 广州 NULL NULL NULL NULL(所影响的行数为 3 行)
--*/
,b.object,b_l_date
from tableA a left join tabkeB b on a.city_id=b.city_id
where b.l_date=condition