再求sql,最近发春,一直会想起好多sql要写

solidvacuum 2012-03-19 06:55:44
某表tb,一列a
a
str1
str2
str3
str4
str5
str6
str7
str8
....
想把每两行的字符串连接起来,如
a
str1str2
str3str4
str5str6
......

再求高手



...全文
121 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2012-03-19
  • 打赏
  • 举报
回复
--> --> (Roy)生成測試數據

if not object_id('Tempdb..#a') is null
drop table #a
Go
Create table #a([Col] nvarchar(4))
Insert #a
select N'str1' union all
select N'str2' union all
select N'str3' union all
select N'str4' union all
select N'str5' union all
select N'str6' union all
select N'str7' union all
select N'str8'
Go
;with a
as
(
Select *,ROW_NUMBER()over(order by getdate()) as row from #a
)
select
a.[Col]+ISNULL(b.[Col],'') as Str1
from a
left join a as b on a.row=b.row-1
where a.row%2=1

/*
str1str2
str3str4
str5str6
str7str8
*/
无涯大者 2012-03-19
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 dawugui 的回复:]
--sql 2005

SQL code

create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb val……
[/Quote]
Up,路上学习!
喜阳阳 2012-03-19
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 dawugui 的回复:]

--sql 2005
SQL code
create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb values('str……
[/Quote]!!
dawugui 2012-03-19
  • 打赏
  • 举报
回复
--sql 2005
create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb values('str5')
insert into tb values('str6')
insert into tb values('str7')
insert into tb values('str8')
go

select a = replace(stuff((select ',' + a from
(
select t.* , px = (row_number() over(order by a) - 1)/2 from tb t
) m where m.px = n.px for xml path('')) , 1 , 1 , ''),',','')
from
(
select t.* , px = (row_number() over(order by a) - 1)/2 from tb t
) n
group by px

drop table tb

/*
a
------------
str1str2
str3str4
str5str6
str7str8

(4 行受影响)
*/
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 dawugui 的回复:]

--sql 2000用函数解决:
SQL code
create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb value……
[/Quote]

++
dawugui 2012-03-19
  • 打赏
  • 举报
回复
--sql 2000用函数解决:
create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb values('str5')
insert into tb values('str6')
insert into tb values('str7')
insert into tb values('str8')
go

--创建函数解决:
create function dbo.f_str(@px int) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str , '') + cast(a as varchar) from (select t.* , px = (((select count(1) from tb where a < t.a) + 1) - 1)/2 from tb t) m where px = @px
return @str
end
go


--调用函数
select dbo.f_str(px) a from
(
select t.* , px = (((select count(1) from tb where a < t.a) + 1) - 1)/2 from tb t
) m
group by px

drop function dbo.f_str

drop table tb

/*

a
----------------------
str1str2
str3str4
str5str6
str7str8

(所影响的行数为 4 行)
*/

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧