A表
UserID name app
1 aaa 12
2 bbb 20
3 ccc 23
4 ddd 25
B表
UserID iMoney TVS
1 300 yes
2 400 no
C表
UserID iKEY TVS
3 AB yes
4 CB no
希望得到最后结果
UserID name app TVS
1 aaa 12 yes
2 bbb 20 no
3 ccc 23 yes
4 ddd 25 no
怎么样用SQL句语解决？请教，先在这里谢谢了。
------解决思路----------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-12-09 15:06:03
-- Version:
-- Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
-- Jul 25 2014 18:52:51
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.4 <X64> (Build 9860: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([UserID] int,[name] varchar(3),[app] int)
insert [A]
select 1,'aaa',12 union all
select 2,'bbb',20 union all
select 3,'ccc',23 union all
select 4,'ddd',25
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([UserID] int,[iMoney] int,[TVS] varchar(3))
insert [B]
select 1,300,'yes' union all
select 2,400,'no'
--> 测试数据：[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([UserID] int,[iKEY] varchar(2),[TVS] varchar(3))
insert [c]
select 3,'AB','yes' union all
select 4,'CB','no'
--------------开始查询--------------------------
--select * from [c]
--select * from [B]
select a.*,isnull(b.tvs,c.tvs)tvs
from [A] a left join b b on a.userid=b.userid
left join c c on a.userid=c.userid
----------------结果----------------------------
/*
UserID name app tvs
----------- ---- ----------- ----
1 aaa 12 yes
2 bbb 20 no
3 ccc 23 yes
4 ddd 25 no
*/
------解决思路----------------------
那句的意思是：如果b.tvs为空，则取c.tvs，否则直接取b.tvs。也就是说，只要b和c的tvs均不同时为null，那么这列就总会有数据
------解决思路----------------------
一般用法是isnull(探测列, 候选值)，意思就是如果探测列有null，则使用候选值补上
------解决思路----------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-12-09 15:50:18
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([UserID] int,[name] varchar(3),[app] int)
insert [A]
select 1,'aaa',12 union all
select 2,'bbb',20 union all
select 3,'ccc',23 union all
select 4,'ddd',25
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([UserID] int,[iMoney] int,[TVS] varchar(3))
insert [B]
select 1,300,'yes' union all
select 2,400,'no'
--> 测试数据：[C]
if object_id('[C]') is not null drop table [C]
go
create table [C]([UserID] int,[iKEY] varchar(2),[TVS] varchar(3))
insert [C]
select 3,'AB','yes' union all
select 4,'CB','no'
--------------开始查询--------------------------
select
a.*,b.TVS
from
a inner join (select UserID,TVS from b union all select UserID,TVS from c) as b on a.UserID=b.UserID
----------------结果----------------------------
/* UserID name app TVS
----------- ---- ----------- ----
1 aaa 12 yes
2 bbb 20 no
3 ccc 23 yes
4 ddd 25 no
(4 行受影响)
*/