要从JOB表取出若干字段,比如A1,A2,A3. 在JOB表中数据,如果A2的值=‘Y’,这个字段的值是空的。
这种情况下,要从Address表中,取相应的值,取值条件是JOB.A1=Address.A1 ,取出Address.A3.
JOB
A1 A2 A3
--- --- ---
100 N 123
200 N 456
300 Y ‘ ’Address
A1 A3
--- ---
300 588输出结果
A1 A2 A3
--- --- ---
100 N 123
200 N 456
300 Y 588这样的SQL语句该怎么写呀。
注:不想用Decode函数实现。谢谢各位大虾了。!!!
这种情况下,要从Address表中,取相应的值,取值条件是JOB.A1=Address.A1 ,取出Address.A3.
JOB
A1 A2 A3
--- --- ---
100 N 123
200 N 456
300 Y ‘ ’Address
A1 A3
--- ---
300 588输出结果
A1 A2 A3
--- --- ---
100 N 123
200 N 456
300 Y 588这样的SQL语句该怎么写呀。
注:不想用Decode函数实现。谢谢各位大虾了。!!!
a.a1,
a.a2,
case when a.a2='Y' then isnull(b.A3) else a.a3 end
from
job a
left join
address b
on
a.a1=b.a1
--> 测试数据:[JOB]
if object_id('[JOB]') is not null drop table [JOB]
create table [JOB]([A1] int,[A2] varchar(1),[A3] varchar(3))
insert [JOB]
select 100,'N','123' union all
select 200,'N','456' union all
select 300,'Y',''
--> 测试数据:[Address]
if object_id('[Address]') is not null drop table [Address]
create table [Address]([A1] int,[A3] int)
insert [Address]
select 300,588select j.A1,j.A2,
A3=case when j.A3='' then A.A3 else j.A3 end
from Job j left join Address a On j.A1=A.A1/*
A1 A2 A3
----------- ---- -----------
100 N 123
200 N 456
300 Y 588(3 行受影响)*/
insert into job(A3) select A3 from Address where JOB.A1=Address.A1
if object_id('[JOB]') is not null drop table [JOB]
go
create table [JOB]([A1] int,[A2] varchar(1),[A3] int)
insert [JOB]
select 100,'N',123 union all
select 200,'N',456 union all
select 300,'Y',''
if object_id('[Address]') is not null drop table [Address]
go
create table [Address]([A1] int,[A3] int)
insert [Address]
select 300,588
---查询---
select
a.a1,
a.a2,
case when a.a2='Y' then isnull(b.A3,0) else a.a3 end as a3
from
job a
left join
address b
on
a.a1=b.a1---结果---
a1 a2 a3
----------- ---- -----------
100 N 123
200 N 456
300 Y 588(所影响的行数为 3 行)
insert into @t
select 100, 'N' , 123
union all select 200, 'N' , 456
union all select 300, 'Y' , null declare @t1 table(A1 int,A2 int)
insert into @t1
select 300, 588
select A1
,A2
,A3 = case when A3 is null then (select top 1 A2 from @t1 where A1 = b.A1) else A3 end
from @t b
A1 A2 A3
----------- ---- -----------
100 N 123
200 N 456
300 Y 588
declare @t table(A1 int , A2 varchar(2), A3 int)
insert into @t
select 100, 'N' , 123
union all select 200, 'N' , 456
union all select 300, 'Y' , null declare @t1 table(A1 int,A2 int)
insert into @t1
select 300, 588
select A1
,A2
,A3 = isnull(A3, (select top 1 A2 from @t1 where A1 = b.A1))
from @t b
select a.A1
,a.A2
,A3 = case when a.A2 = 'Y' then b.A2 else a.A3 end
from @t a
left join @t1 b on a.A1 = b.A1A1 A2 A3
----------- ---- -----------
100 N 123
200 N 456
300 Y 588(所影响的行数为 3 行)
insert into JOB
select 100, 'N' , 123 union all
select 200, 'N' , 456 union all
select 300, 'Y' , null create table Address(A1 int,A2 int)
insert into Address
select 300, 588
select A1,A2,A3 = case when A3 is null then
(select top 1 A2 from Address where A1 = JOB.A1) else A3 end
from JOB A1 A2 A3
100 N 123
200 N 456
300 Y 588
1,刚才只是举个例子,实际上A3项不止一个,有10几个,都是受A2=Y是,就是空,要从Address表中取值。那对于这些项目,每一个都要判断一下A2的值吗。
2,上述的写法,case when A3 …… 和用DECODE函数,哪个效率会高些。我就是不想每个项目输出的时候都要判断一下A2的值
select * from job where a2='N'
union all
select a.a1,a.a2,isnull(b.A3,0) from job a,address b where a.a1=b.a1 and a.a2='Y'/**
A1 A2 A3
----------- ---- -----------
100 N 123
200 N 456
300 Y 588(所影响的行数为 3 行)
**/