题目如下:
第一个表(Person):
------------------------------------------------
ID FirstName LastName
------------------------------------------------
1 David Backham
2 ED Jason
3 Nan Xiao
4 Liu Endy
5 Wang Joe
6 Chang Lucy
------------------------------------------------第二张表(Country):
--------------------------------------------------------
国家 name1 name2 name3 name4 name5
--------------------------------------------------------
巴西 1 2 null null null
武汉 3 4 5 6 null
--------------------------------------------------------问题:要求写一个SQL,能够得出下面的结果:
--------------------------------------------------------
国家 名称
--------------------------------------------------------
巴西 David Backham|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
--------------------------------------------------------希望高手(大牛人)能够帮我,谢谢!!!
第一个表(Person):
------------------------------------------------
ID FirstName LastName
------------------------------------------------
1 David Backham
2 ED Jason
3 Nan Xiao
4 Liu Endy
5 Wang Joe
6 Chang Lucy
------------------------------------------------第二张表(Country):
--------------------------------------------------------
国家 name1 name2 name3 name4 name5
--------------------------------------------------------
巴西 1 2 null null null
武汉 3 4 5 6 null
--------------------------------------------------------问题:要求写一个SQL,能够得出下面的结果:
--------------------------------------------------------
国家 名称
--------------------------------------------------------
巴西 David Backham|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
--------------------------------------------------------希望高手(大牛人)能够帮我,谢谢!!!
--创建函数
CREATE function f_get_name
(@id int)
returns nvarchar(1000)
as
begin
if @id is null
return ''
else
declare @name nvarchar(1000)
select @name=FirstName+' '+LastName
from person
where id=@id
order by id return @name
end----执行查询
set nocount on
create table person(id int,FirstName nvarchar(10),LastName nvarchar(10))
insert person select 1,'David','LastName'
union all select 2, 'ED', 'Jason'
union all select 3,'Nan', 'Xiao'
union all select 4,'Liu', 'Endy'
union all select 5,'Wang', 'Joe'
union all select 6,'Chang','Lucy'create table #Country(Country nvarchar(10),name1 int,name2 int,name3 int,name4 int,name5 int)
insert #Country select '巴西',1,2,null,null,null
union all select '武汉',3,4,5,6,null
select country,dbo.f_get_name(name1)+'|'+dbo.f_get_name(name2)+'|'+dbo.f_get_name(name3)+'|'+dbo.f_get_name(name4)+'|'+dbo.f_get_name(name5)
from #country--drop function f_get_name
drop table person
drop table #countryset nocount off
结果如下:
巴西
David LastName|ED Jason||| 武汉
Nan Xiao|Liu Endy|Wang Joe|Chang Lucy| 让我满意,因为多出几个:|||
(case when len(dbo.f_get_name(name1)) > 0 then dbo.f_get_name(name1) + '|' else '' end +
case when len(dbo.f_get_name(name2)) > 0 then dbo.f_get_name(name2) + '|' else '' end +
case when len(dbo.f_get_name(name3)) > 0 then dbo.f_get_name(name3) + '|' else '' end +
case when len(dbo.f_get_name(name4)) > 0 then dbo.f_get_name(name4) + '|' else '' end +
case when len(dbo.f_get_name(name5)) > 0 then dbo.f_get_name(name5) + '|' else '' end) as '名称'
from #country
巴西 David LastName|ED Jason|
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy|
create table #temp1(
country varchar(10),
name1 int,
name2 int,
name3 int,
name4 int,
name5 int,
name1_1 varchar(20),
name2_1 varchar(20),
name3_1 varchar(20),
name4_1 varchar(20),
name5_1 varchar(20)
)
insert into #temp1(country,name1,name2,name3,name4,name5) select country,name1,name2,name3,name4,name5 from countryupdate #temp1 from table1 set #temp1.name1_1=table1.firstname+' '+table1.lastname where #temp1.name1=table1.id
update #temp1 from table1 set #temp1.name2_1=table1.firstname+' '+table1.lastname where #temp1.name2=table1.id
update #temp1 from table1 set #temp1.name3_1=table1.firstname+' '+table1.lastname where #temp1.name3=table1.id
update #temp1 from table1 set #temp1.name4_1=table1.firstname+' '+table1.lastname where #temp1.name4=table1.id
update #temp1 from table1 set #temp1.name5_1=table1.firstname+' '+table1.lastname where #temp1.name5=table1.idupdate #temp1 set name1_1=name1_1+'|' where name1_1 is not null
update #temp1 set name2_1=name2_1+'|' where name2_1 is not null
update #temp1 set name3_1=name3_1+'|' where name3_1 is not null
update #temp1 set name4_1=name4_1+'|' where name4_1 is not null
update #temp1 set name5_1=name5_1+'|' where name5_1 is not nullselect country,isnull(name1_1,'')+isnull(name2_1,'')+isnull(name3_1,'')+isnull(name4_1,'')+isnull(name5_1,'') from #temp1这个方法的好处就是不用游标,效率会比你那个稍微快点,当然这个方法仅仅针对你的name字段数是固定的情况,如果不是固定的那还需要另外的处理。
create table person(id int,FirstName nvarchar(10),LastName nvarchar(10))
insert person select 1,'David','LastName'
union all select 2, 'ED', 'Jason'
union all select 3,'Nan', 'Xiao'
union all select 4,'Liu', 'Endy'
union all select 5,'Wang', 'Joe'
union all select 6,'Chang','Lucy'create table Country(Country nvarchar(10),name1 int,name2 int,name3 int,name4 int,name5 int)
insert Country select '巴西',1,2,null,null,null
union all select '武汉',3,4,5,6,nullselect
country as '国家',
(select FirstName+' '+LastName from person where id=name1)
+isnull('|'+(select FirstName+' '+LastName from person where id=name2),'')+
+isnull('|'+(select FirstName+' '+LastName from person where id=name3),'')+
+isnull('|'+(select FirstName+' '+LastName from person where id=name4),'')+
+isnull('|'+(select FirstName+' '+LastName from person where id=name5),'') as '名称'
from
countrygo
drop table person,country/*
国家 名称
---------- --------
巴西 David LastName|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
*/
--创建函数
CREATE function f_get_name
(@id int)
returns nvarchar(1000)
as
begin
if @id is null
return ''
else
declare @name nvarchar(1000)
select @name=FirstName+' '+LastName
from person
where id=@id
order by id return @name
end---执行查询
set nocount on
create table person(id int,FirstName nvarchar(10),LastName nvarchar(10))
insert person select 1,'David','LastName'
union all select 2, 'ED', 'Jason'
union all select 3,'Nan', 'Xiao'
union all select 4,'Liu', 'Endy'
union all select 5,'Wang', 'Joe'
union all select 6,'Chang','Lucy'create table #Country(Country nvarchar(10),name1 int,name2 int,name3 int,name4 int,name5 int)
insert #Country select '巴西',1,2,null,null,null
union all select '武汉',3,4,5,6,null
select country as '国家',(isnull(dbo.f_get_name(name1),'')+isnull('|'+dbo.f_get_name(name2),'')+isnull('|'+dbo.f_get_name(name3),'') +isnull('|'+dbo.f_get_name(name4),'')+isnull('|'+dbo.f_get_name(name5),'')) as '名称'
from #country--drop function f_get_name
drop table person
drop table #countryset nocount off
巴西 David LastName|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
(
select 国家,
isnull(n1 + '|','') +
isnull(n2 + '|','') +
isnull(n3 + '|','') +
isnull(n4 + '|','') +
isnull(n5 + '|','') AS N
form (select 国家,
(select firstname + ' ' + lastname from person where id = country.name1) as N1,
(select firstname + ' ' + lastname from person where id = country.name2) as N2,
(select firstname + ' ' + lastname from person where id = country.name3) as N3,
(select firstname + ' ' + lastname from person where id = country.name4) as N4,
(select firstname + ' ' + lastname from person where id = country.name5) as N5
from country ) t1
) t2
print @str结果为:null我想问的是:是不是所有字符与null相加之后都会变为null?