有三个表
表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要怎么写
表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要怎么写
--> 测试数据: @表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' --可以把参数改成BBBselect
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
*/
应该是这样的
我想联合表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 0
--> 测试数据: @表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' --可以把参数改成BBBselect
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
*/
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',0if 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
*/
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 @C VARCHAR(3) = N'BBB';
--当c3 ='BBB'
SELECT
T1.id,
T1.c1,
T3.c3,
CASE WHEN T3.c3 = @C THEN 1 ELSE 0 END
FROM @表1 AS T1
FULL JOIN @表3 AS T3
ON T1.id = T3.id
id c1 c3
----------- ---- ---- -----------
1 a AAA 0
2 b BBB 1
3 c AAA 0
4 d AAA 0
5 e NULL 0(5 row(s) affected)