高效SQL——从无主键表中合并字段值
以下是引用片段: create table #T1 ( A varchar(10), B varchar(20) ) insert into #T1 values ('aa','1') insert into #T1 values ('aa','9a') insert into #T1 values ('bb','1') insert into #T1 values ('bb','10') insert into #T1 values ('bb','16') insert into #T1 values ('aa','16') insert into #T1 values ('aa','17') insert into #T1 values ('aa','30') insert into #T1 values ('bb','6df') insert into #T1 values ('aa','5') insert into #T1 values ('aa','8') insert into #T1 values ('aa','ed') |
所要的结果:
aa 1,9a,16,17,30,5,8,ed
bb 1,10,16,6df
解决方法:
以下是引用片段: declare @c varchar(1024) set @c='' declare @x char(10),@y char(10) set @x='' set @y='' select @y=@x,@x=x.a,@c=@c + (case @x when @y then ',' else ';' + x.a + ':' end) +x.d from (select b as d,a from #t1) as x order by x.a set @c=substring(@c,2,len(@c)-1) select @c |
为什么说是高效呢?
因为摒弃了游标和函数的途径,而采用变量的方式来保存值
也就是说避免了游标和函数自身的缺点
如果是简单地把字段值合并的sql语句:
select c1||c2||c3 as name from table
中间可以添加字符常量和数字,如
select c1||'test'||c2||c3 as name from table
select c1||2||c2||c3 as name from table
如何用一条SQL语句,将多条记录(一个字段)合并为一个?
例如:
table字段为:tableID(nchar)
查询结果为不确定的多条:
tableID
T1
T2
T3
T4
……
如何用一条SQL语句将这些记录合并为一个字段,值为:'T1T2T3……'
以下是引用片段: create table t (tableid nchar(30)) insert t select 'T1' union all select 'T2' union all select 'T3' union all select 'T4' union all select 'T5' union all select 'T6' go create function f_he() returns @t table(col varchar(50)) as begin declare @sql varchar(50) set @sql='' select @sql=@sql+ltrim(rtrim(tableid)) from t insert @t values (@sql) return end go select * from t select * from dbo.f_he() drop function f_he drop table t col -------------------------------------------------- T1T2T3T4T5T6 (所影响的行数为 1 行) |