create table t(id int,name nvarchar(30))
create table t1(id int,SubCom int, EarningOrg int)
create table t2(id int, name nvarchar(30))
insert into t
select 1, '总公司' union all
select 2, '公司1' union all
select 3, '公司2'
insert into t1
select 1, 1, 258 union all
select 2, 1 ,260 union all
select 3, 1 ,262 union all
select 4, 1 ,263 union all
select 5, 1 ,265 union all
select 6, 1 ,266 union all
select 7, 1 ,267 union all
select 8, 1 ,268 union all
select 9, 1 ,271 union all
select 10, 1, 272 union all
select 11, 2, 408 union all
select 12, 3, 422
insert into t2
select 258, '一车队' union all
select 260, '二车队' union all
select 262, '三车队' union all
select 263, '四车队' union all
select 265, '五车队' union all
select 266, '六车队' union all
select 267, '七车队' union all
select 268, '八车队' union all
select 271, '九车队' union all
select 272, '十车队'
create function gY(@id as int)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
set @s=''
select @s=@s+ISNULL(t2.name+',','') from t1,t2 where t1.SubCom=@id and t1.EarningOrg=t2.id
IF @s<>''
return substring(@s,1,len(@s)-1)
return ''
end
go
select name,基础name=dbo.gY(id) from t where dbo.gY(id)<>''
drop table t
drop table t1
drop table t2
create table t1(id int,SubCom int, EarningOrg int)
create table t2(id int, name nvarchar(30))
insert into t
select 1, '总公司' union all
select 2, '公司1' union all
select 3, '公司2'
insert into t1
select 1, 1, 258 union all
select 2, 1 ,260 union all
select 3, 1 ,262 union all
select 4, 1 ,263 union all
select 5, 1 ,265 union all
select 6, 1 ,266 union all
select 7, 1 ,267 union all
select 8, 1 ,268 union all
select 9, 1 ,271 union all
select 10, 1, 272 union all
select 11, 2, 408 union all
select 12, 3, 422
insert into t2
select 258, '一车队' union all
select 260, '二车队' union all
select 262, '三车队' union all
select 263, '四车队' union all
select 265, '五车队' union all
select 266, '六车队' union all
select 267, '七车队' union all
select 268, '八车队' union all
select 271, '九车队' union all
select 272, '十车队'
create function gY(@id as int)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
set @s=''
select @s=@s+ISNULL(t2.name+',','') from t1,t2 where t1.SubCom=@id and t1.EarningOrg=t2.id
IF @s<>''
return substring(@s,1,len(@s)-1)
return ''
end
go
select name,基础name=dbo.gY(id) from t where dbo.gY(id)<>''
drop table t
drop table t1
drop table t2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货