现有tableA,tableB ,tableA (id ,name,sex ,age),tableB(id ,name,gongzi, buzhu)
tableA 内容：
1 tom m 23
2 nike w 24
3 rose w 34
4 lib m 40
tableB 内容：
1 tom 100 200
2 nike 200 120
3 lib 200 230
现要求查询结果显示：
1 tom m 23 100 200
2 nike w 24 200 120
3 rose w 34 0 0
4 lib m 40 200 230
sqlserer?? 查询统计
------解决方案--------------------
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-03 14:20:17
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[tableB]
if object_id('[tableB]') is not null drop table [tableB]
go
create table [tableB]([id] int,[name] varchar(4),[gongzi] int,[buzhu] int)
insert [tableB]
select 1,'tom',100,200 union all
select 2,'nike',200,120 union all
select 3,'lib',200,230
--------------开始查询--------------------------
--> 测试数据：[tableA]
if object_id('[tableA]') is not null drop table [tableA]
go
create table [tableA]([id] int,[name] varchar(4),[sex] varchar(1),[age] int)
insert [tableA]
select 1,'tom','m',23 union all
select 2,'nike','w',24 union all
select 3,'rose','w',34 union all
select 4,'lib','m',40
--------------开始查询--------------------------
select a.id,a.NAME,a.sex,a.age,ISNULL(b.gongzi,0),ISNULL(b.buzhu,0)
from [tableA] a LEFT JOIN tableb b ON a.NAME=b.name
----------------结果----------------------------
/*
id NAME sex age
----------- ---- ---- ----------- ----------- -----------
1 tom m 23 100 200
2 nike w 24 200 120
3 rose w 34 0 0
4 lib m 40 200 230