1,
create function getstr(@id Nchar(10))
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
if @id is null or @id=''
begin
set @str='无'
return @str
end
select @str=rtrim(col2) from 表2
where col1=@id
return @str
end
GO2,
select dbo.getstr(col1),dbo.getstr(col2)......from 表演
create function getstr(@id Nchar(10))
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
if @id is null or @id=''
begin
set @str='无'
return @str
end
select @str=rtrim(col2) from 表2
where col1=@id
return @str
end
GO2,
select dbo.getstr(col1),dbo.getstr(col2)......from 表演
isnull((select col2 from 表二 where col1=表一.col1),'无') col1,
isnull((select col2 from 表二 where col1=表一.col2),'无') col2,
isnull((select col2 from 表二 where col1=表一.col3),'无') col3,
isnull((select col2 from 表二 where col1=表一.col4),'无') col4,
isnull((select col2 from 表二 where col1=表一.col5),'无') col5
from 表一
isnull((select col2 from 表二 where col1=表一.col1),'无') col1,
isnull((select col2 from 表二 where col1=表一.col2),'无') col2,
isnull((select col2 from 表二 where col1=表一.col3),'无') col3,
isnull((select col2 from 表二 where col1=表一.col4),'无') col4,
isnull((select col2 from 表二 where col1=表一.col5),'无') col5
from 表一
returns varchar(20)
as
begin
declare @re varchar(20)
select @re=col2 from 表2 where col1=@coln
if @re is null
begin
set @re='无'
end
return @re
end
--------------------------------------------------------------------
select dbo.chanNum(col1),dbo.chanNum(col2),dbo.chanNum(col3)... from dbo.表1
不行啊?
错误消息:
服务器: 消息 512
,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
isnull((select top 1 col2 from 表二 where col1=表一.col1),'无') col1,
isnull((select top 1 col2 from 表二 where col1=表一.col2),'无') col2,
isnull((select top 1 col2 from 表二 where col1=表一.col3),'无') col3,
isnull((select top 1 col2 from 表二 where col1=表一.col4),'无') col4,
isnull((select top 1 col2 from 表二 where col1=表一.col5),'无') col5
from 表一
isnull((select col2 from table2 where coll = table1.coll), '空')
, isnull((select col2 from table2 where coll = table1.col1), '空')
, isnull((select col2 from table2 where coll = table1.col2), '空')
, isnull((select col2 from table2 where coll = table1.col3), '空')
, isnull((select col2 from table2 where coll = table1.col4), '空')
from table1
col1=(select col2=(case when col2 is null then '无' else col2 end) from two where one.col1=two.col1),
col2=(select col2=(case when col2 is null then '无' else col2 end) from two where one.col2=two.col1),
col3=(select col2=(case when col2 is null then '无' else col2 end) from two where one.col3=two.col1),
col4=(select col2=(case when col2 is null then '无' else col2 end) from two where one.col4=two.col1),
col5=(select col2=(case when col2 is null then '无' else col2 end) from two where one.col5=two.col1)
from one已经测试通过
isnull((select col2 from table2 where col1=A.col1),'无') col1,
isnull((select col2 from table2 where col1=A.col2),'无') col2,
isnull((select col2 from table2 where col1=A.col3),'无') col3,
isnull((select col2 from table2 where col1=A.col4),'无') col4,
isnull((select col2 from table2 where col1=A.col5),'无') col5
from table1 A
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
if len(@str)=0
set @str='无'
else
select @str=rtrim(col2) from 表2 where col1=@id
return @str
end
GO
select dbo.getstr(col1),dbo.getstr(col2)......from 表演
isnull(b1.col2,'无') col1,
isnull(b2.col2,'无') col2,
isnull(b3.col2,'无') col3,
isnull(b4.col2,'无') col4,
isnull(b5.col2,'无') col5
from table1 a
left join table2 b1
on a.col1=b1.col1
left join table2 b2
on a.col2=b2.col1
left join table2 b3
on a.col3=b3.col1
left join table2 b4
on a.col4=b4.col1
left join table2 b5
on a.col5=b5.col1