日期:2014-05-17  浏览次数:20793 次

求两表联全的sql
有三个表
表1
1 a 0
2 b 0
3 c 0
4 d 0
5 e 0


表2
1 AAA
2 BBB


表3
1 a AAA
2 b BBB
3 c AAA
4 d AAA


我想联合表1和表3查询AAA的结果如下:
1 a Null 0
2 b BBB 1
3 c Null 0
4 d Null 0
5 e Null 1

或者查询BBB的结果如下:
1 a AAA 1
2 b Null 0
3 c AAA 1
4 d AAA 1
5 e Null 0


这个sql要怎么写

------解决方案--------------------
SQL code

--> 测试数据: @表1
declare @表1 table (id int,c1 varchar(1),c2 int)
insert into @表1
select 1,'a',0 union all
select 2,'b',0 union all
select 3,'c',0 union all
select 4,'d',0 union all
select 5,'e',0

--> 测试数据: @表3
declare @表3 table (id int,c1 varchar(1),c3 varchar(3))
insert into @表3
select 1,'a','AAA' union all
select 2,'b','BBB' union all
select 3,'c','AAA' union all
select 4,'d','AAA'

declare @sql varchar(20) set @sql='AAA' --可以把参数改成BBB

select 
    a.id,a.c1,
    case when b.c3=@sql then null else b.c3 end as c2,
    case when b.c3 is null or b.c3<>@sql then 1 else 0 end as c3
from @表1 a left join @表3 b on a.id=b.id
/*
id          c1   c2   c3
----------- ---- ---- -----------
1           a    NULL 0
2           b    BBB  1
3           c    NULL 0
4           d    NULL 0
5           e    NULL 1
*/

------解决方案--------------------
SQL code

--> 测试数据: @表1
declare @表1 table (id int,c1 varchar(1),c2 int)
insert into @表1
select 1,'a',0 union all
select 2,'b',0 union all
select 3,'c',0 union all
select 4,'d',0 union all
select 5,'e',0

--> 测试数据: @表3
declare @表3 table (id int,c1 varchar(1),c3 varchar(3))
insert into @表3
select 1,'a','AAA' union all
select 2,'b','BBB' union all
select 3,'c','AAA' union all
select 4,'d','AAA'

declare @sql varchar(20) set @sql='AAA' --可以把参数改成BBB

select 
    a.id,a.c1,
    case when b.c3=@sql then b.c3 else null end as c2,
    case when b.c3=@sql then 1 else 0 end as c3
from @表1 a left join @表3 b on a.id=b.id
/*
id          c1   c2   c3
----------- ---- ---- -----------
1           a    AAA  1
2           b    NULL 0
3           c    AAA  1
4           d    AAA  1
5           e    NULL 0
*/

------解决方案--------------------
SQL code

if object_id=('tab1') is not null
drop table tab1
go
create table tab1(id int,c1 varchar(1),c2 int)
insert into tab1
select 1,'a',0 union all
select 2,'b',0 union all
select 3,'c',0 union all
select 4,'d',0 union all
select 5,'e',0

if object_id=('tab3') is not null
drop table tab3
go
create table tab3(id int,c1 varchar(1),c2 int)
insert into tab3
select 1,'a','AAA' union all
select 2,'b','BBB' union all
select 3,'c','AAA' union all
select 4,'d','AAA'

--开始查询
SELECT a.id,
       a.c1,
       CASE 
         WHEN b.c3 ='AAA' --此处可更换'BBB'
         THEN b.c3
         ELSE NULL
       END AS c2,
       CASE 
         WHEN b.c3 ='AAA' --此处可更换'BBB'
         THEN 1
         ELSE 0
       END AS c3
FROM   tab1 a,tab3 b
WHERE a.id = b.id

/*
id          c1   c2   c3
----------- ---- ---- -----------
1           a    AAA  1
2           b    NULL 0
3           c    AAA  1
4           d    AAA  1
5           e    NULL 0
*/

------解决方案--------------------
探讨
这个表3里不止AAA和BBB,还有别的数值

这里道个歉上面那个结果写反了
应该是这样的
我想联合表1和表3查询BBB的结果如下:
1 a Null 0
2 b BBB 1
3 c Null 0
4 d Null 0
5 e Null 1

或者查询AAA的结果如下:
1 a AAA 1
2 b Null 0
3 c AAA 1
4 d AAA 1
5 e Null……

------解决方案--------------------
select a.id,a.col,b.cols,(cast when isnull(cols,'')='' then 0 else then 1 end) from 表1 a left join 表3 on a.id=b.id