select isnull(a.value,'')+isnull(b.value,'') as newvalue from [table] a left join [table] b on a.id=b.id+1
这个出来的结果貌似是 ab bc cd d
DECLARE @T TABLE(id INT,[VALUE] VARCHAR(10)) INSERT @T SELECT 1 , 'a' INSERT @T SELECT 2 , 'b' INSERT @T SELECT 3 , 'c' INSERT @T SELECT 4, 'd' SELECT ISNULL(A.[VALUE]+B.[VALUE],A.[VALUE]) FROM @T A LEFT JOIN @T B ON A.ID<B.ID /*-------------------- ab ac ad bc bd cd d(7 行受影响)*/
create table [table]( id int,[VALUE] varchar(10)) insert [table] select 1 , 'a' insert [table] select 2 , 'b' insert [table] select 3 , 'c' insert [table] select 4 , 'd'select isnull(a.value,'')+isnull(b.value,'') as newvalue from (select [value] from [table]) a left join (select [value] from [table]) b on a.[value]<b.[value] order by isnull(a.value,'')+isnull(b.value,'')drop table [table]newvalue -------------------- ab ac ad bc bd cd d(7 行受影响)
--建立测试环境 set nocount on create table test(id varchar(20),VALUE varchar(20)) insert into test select '1','a' insert into test select '2','b' insert into test select '3','c' insert into test select '4','d' go --测试 select a.value+b.value from (select * from test union select 3,'') a,test b where a.id<b.id order by case when a.value='' then 'x' else a.value end --删除测试环境 drop table test set nocount off/*ab ac ad bc bd cd d */
这个出来的结果貌似是 ab bc cd d
INSERT @T SELECT 1 , 'a'
INSERT @T SELECT 2 , 'b'
INSERT @T SELECT 3 , 'c'
INSERT @T SELECT 4, 'd'
SELECT ISNULL(A.[VALUE]+B.[VALUE],A.[VALUE]) FROM @T A LEFT JOIN @T B ON A.ID<B.ID
/*--------------------
ab
ac
ad
bc
bd
cd
d(7 行受影响)*/
insert [table] select 1 , 'a'
insert [table] select 2 , 'b'
insert [table] select 3 , 'c'
insert [table] select 4 , 'd'select isnull(a.value,'')+isnull(b.value,'') as newvalue
from (select [value] from [table]) a left join
(select [value] from [table]) b
on a.[value]<b.[value]
order by isnull(a.value,'')+isnull(b.value,'')drop table [table]newvalue
--------------------
ab
ac
ad
bc
bd
cd
d(7 行受影响)
set nocount on
create table test(id varchar(20),VALUE varchar(20))
insert into test select '1','a'
insert into test select '2','b'
insert into test select '3','c'
insert into test select '4','d'
go
--测试
select a.value+b.value from
(select * from test union select 3,'') a,test b
where a.id<b.id
order by case when a.value='' then 'x' else a.value end
--删除测试环境
drop table test
set nocount off/*ab
ac
ad
bc
bd
cd
d
*/