use tempdb
go
create table a (m1 int,n1 int)
insert a
select 1,2
union all
select 2,3create table a1(s1 varchar(10))
insert a1
select 'd'create table a2(s2 varchar(10))
insert a2
select 'f'
go
select
a.*,s=isnull(a1.s1,a2.s2)
from
a
left join
a1 on a.m1=1
left join
a2 on a.m1=2
m1 n1 s
----------- ----------- ----------
1 2 d
2 3 f(2 個資料列受到影響)
go
create table a (m1 int,n1 int)
insert a
select 1,2
union all
select 2,3create table a1(s1 varchar(10))
insert a1
select 'd'create table a2(s2 varchar(10))
insert a2
select 'f'
go
select
a.*,s=isnull(a1.s1,a2.s2)
from
a
left join
a1 on a.m1=1
left join
a2 on a.m1=2
m1 n1 s
----------- ----------- ----------
1 2 d
2 3 f(2 個資料列受到影響)
select
a.*,L=isnull(a1.s1,a2.s2)
from
a
left join
a1 on a.m1=1
left join
a2 on a.m1=2
select *,Fun1 (m1)as s from a
insert a
select 1,2
union all
select 2,3create table a1(s1 varchar(10))
insert a1
select 'd'create table a2(s2 varchar(10))
insert a2
select 'f'create table a3(s3 varchar(10))
insert a3
select 'x'
go
declare @s varchar(max)
select @s=isnull(@s + ' union all ' ,'') + ' select m1,n1,f=(select top 1 s' + rtrim(m1) + ' from a' + rtrim(m1) + ') from a' from a
exec(@s)
/*
1 2 d
2 3 d
1 2 f
2 3 f
*/
drop table a,a1,a2,a3
go
insert a
select 1,2
union all
select 2,3create table a1(s1 varchar(10))
insert a1
select 'd'create table a2(s2 varchar(10))
insert a2
select 'f'create table a3(s3 varchar(10))
insert a3
select 'x'
go
declare @s varchar(max)
select @s=isnull(@s + ' union all ' ,'') + ' select m1,n1,f=(select top 1 s' + rtrim(m1) + ' from a' + rtrim(m1) + ') from a where m1=' + rtrim(m1) from a
print @s
exec(@s)
/*
1 2 d
2 3 f
*/
drop table a,a1,a2,a3
go
DECLARE curFetchBranchCD CURSOR
FOR
SELECT
m1
FROM a
WHERE n1 = ''OPEN curFetchBranchCDFETCH NEXT FROM curFetchBranchCD
INTO @BranchCD
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @SQL VARCHAR(8000)
SET @SQL ='
SELECT
s
FROM
a_'+CONVERT(VARCHAR(6),@BranchCD)+'' EXEC (@SQL)
END
FETCH NEXT FROM curFetchBranchCD
INTO @BranchCDCLOSE curFetchBranchCD
DEALLOCATE curFetchBranchCD