小SQL大功能
SQL问题:有Tabel T(c1 int, c2 nvarchar(50), c3 int)
c1 c2 c3
1 How 1
2 are 1
3 you 1
4 Fine 2
5 thanks 2
6 And 2
7 you 2
8 I 3
9 am 3
10 fine 3
11 too 3
想得到如下结果:
How are you
Fine thanks And you
I am fine too
以上问题如果用游标,临时表等等来实现,那是相当简单,但是游标和临时表都太占用资源,浪费性能,其实可以用简单的SQL语句来实现,完整的例子如下(SQL Server 2005实现):
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T]') AND type in (N'U'))
2DROP TABLE [dbo].[T]
3GO
4
5create table T(
6 c1 int not null,
7 c2 nvarchar(50) not null,
8 c3 int not null
9)
10GO
11
12insert into T(c1, c2, c3)
13select 1, 'How', 1 union
14select 2, 'are', 1 union
15select 3, 'you', 1 union
16select 4, 'Fine', 2 union
17select 5, 'thanks', 2 union
18select 6, 'And', 2 union
19select 7, 'you', 2 union
20select 8, 'I', 3 union
21select 9, 'am', 3 union
22select 10, 'fine', 3 union
23select 11, 'too', 3
24GO
25
26declare @s nvarchar(300), @idx int
27set @s=''
28set @idx=0 -- 可以为任何值
29
30select @s=@s
31 + case @idx when c3 then ' ' else char(10) end
32 + c2,
33 @idx = c3
34from T
35
36set @s=stuff(@s, 1, 1, '') -- 去@s首字符,为' '或为char(10)
37print @s -- 打印查看结果
SQL问题:有Tabel T(c1 int, c2 nvarchar(50), c3 int)
c1 c2 c3
1 How 1
2 are 1
3 you 1
4 Fine 2
5 thanks 2
6 And 2
7 you 2
8 I 3
9 am 3
10 fine 3
11 too 3
想得到如下结果:
How are you
Fine thanks And you
I am fine too
以上问题如果用游标,临时表等等来实现,那是相当简单,但是游标和临时表都太占用资源,浪费性能,其实可以用简单的SQL语句来实现,完整的例子如下(SQL Server 2005实现):
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T]') AND type in (N'U'))
2DROP TABLE [dbo].[T]
3GO
4
5create table T(
6 c1 int not null,
7 c2 nvarchar(50) not null,
8 c3 int not null
9)
10GO
11
12insert into T(c1, c2, c3)
13select 1, 'How', 1 union
14select 2, 'are', 1 union
15select 3, 'you', 1 union
16select 4, 'Fine', 2 union
17select 5, 'thanks', 2 union
18select 6, 'And', 2 union
19select 7, 'you', 2 union
20select 8, 'I', 3 union
21select 9, 'am', 3 union
22select 10, 'fine', 3 union
23select 11, 'too', 3
24GO
25
26declare @s nvarchar(300), @idx int
27set @s=''
28set @idx=0 -- 可以为任何值
29
30select @s=@s
31 + case @idx when c3 then ' ' else char(10) end
32 + c2,
33 @idx = c3
34from T
35
36set @s=stuff(@s, 1, 1, '') -- 去@s首字符,为' '或为char(10)
37print @s -- 打印查看结果