实现以下表中Msg字段的拼接: ID Time Msg
1 2009-1-1 10:10:11:10 1/3 中华
1 2009-1-1 10:10:11:20 2/3 人民
1 2009-1-1 10:10:11:30 3/3 共和国
2 2009-2-2 10:10:11:10 1/5 t1
2 2009-2-2 10:10:11:20 2/5 t2
2 2009-2-2 10:10:11:30 3/5 t3
2 2009-2-2 10:10:11:40 4/5 t4
2 2009-2-2 10:10:11:10 5/5 t5
3 2009-3-3 10:10:11:10 1/7 st1
3 2009-3-3 10:10:11:10 2/7 st2
3 2009-3-3 10:10:11:10 3/7 st3
3 2009-3-3 10:10:11:10 4/7 st4
3 2009-3-3 10:10:11:10 5/7 st5
3 2009-3-3 10:10:11:10 6/7 st6
3 2009-3-3 10:10:11:10 7/7 st7
4 2009-4-3 10:10:11:10 test1 实现的效果:将上表中ID号相同记录的Msg的内容拼接起来(去掉前面的分数和空格),并且显示由几个字段拼接的,Time字段,选第一个
拼接后的效果如下:
ID Time MsgMerge Count
1 2009-1-1 10:10:11:10 中华人民共和国 3
2 2009-2-2 10:10:11:10 t1t2t3t4t5 5
3 2009-3-3 10:10:11:10 st1st2st3st4st5st6st7 7
4 2009-4-3 10:10:11:10 test1 1希望哪为高手能提供一个高效率的方法,我要处理的实际数据有好几万条,涉及好几张表,所以执行时间越短越好。谢谢!
1 2009-1-1 10:10:11:10 1/3 中华
1 2009-1-1 10:10:11:20 2/3 人民
1 2009-1-1 10:10:11:30 3/3 共和国
2 2009-2-2 10:10:11:10 1/5 t1
2 2009-2-2 10:10:11:20 2/5 t2
2 2009-2-2 10:10:11:30 3/5 t3
2 2009-2-2 10:10:11:40 4/5 t4
2 2009-2-2 10:10:11:10 5/5 t5
3 2009-3-3 10:10:11:10 1/7 st1
3 2009-3-3 10:10:11:10 2/7 st2
3 2009-3-3 10:10:11:10 3/7 st3
3 2009-3-3 10:10:11:10 4/7 st4
3 2009-3-3 10:10:11:10 5/7 st5
3 2009-3-3 10:10:11:10 6/7 st6
3 2009-3-3 10:10:11:10 7/7 st7
4 2009-4-3 10:10:11:10 test1 实现的效果:将上表中ID号相同记录的Msg的内容拼接起来(去掉前面的分数和空格),并且显示由几个字段拼接的,Time字段,选第一个
拼接后的效果如下:
ID Time MsgMerge Count
1 2009-1-1 10:10:11:10 中华人民共和国 3
2 2009-2-2 10:10:11:10 t1t2t3t4t5 5
3 2009-3-3 10:10:11:10 st1st2st3st4st5st6st7 7
4 2009-4-3 10:10:11:10 test1 1希望哪为高手能提供一个高效率的方法,我要处理的实际数据有好几万条,涉及好几张表,所以执行时间越短越好。谢谢!
解决方案 »
- 一个关于统计的问题
- 这个简单查询里面的临时表换成表变量的写法怎么写?
- exec pro_cx '149' 不允许从数据类型 varchar 到 money 的隐性转换。请使用 CONVERT 函数来运行此查询?
- 求教:按时间段查询时碰到一个新问题,请大家帮帮忙?
- 关于查询结果的显示顺序问题(在线等!!)
- sqlstr1的查询结果是另外一条查询语句sqlstr2,如何在SQL中来执行sqlstr2
- sql server 怎样安装在win2000 professional下?
- 处理分类拼接字段值的解法
- 大数据量操作经常无反应
- SQL与ACCESS间的转换问题(在线)
- 有些对default的不明白,请各位帮忙!!谢谢
- select name from northwind..sysobjects ..的问题
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-04 15:19:28.123●●●●●
★★★★★soft_wsx★★★★★
*/
if object_ID('TB') IS NOT NULL DROP TABLE TB
create table tb(id int,corpname varchar(10),salebrand varchar(10))
insert tb
select 1, '化成公司', '阿迪'
union all select 2, '化成公司', '耐克'
union all select 3, '化成公司', '奔驰'
union all select 4, '天意公司', '宝马'
union all select 5, '长空公司', '奥迪'
go
if object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@corpname nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+salebrand from tb where corpname=@corpname
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goif object_id('f_tb2') is not null drop function f_tb2
go
create function f_tb2(@corpname nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+cast(id as varchar) from tb where corpname=@corpname
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goselect dbo.f_tb2(corpname) as 公司代码,corpname as 公司,dbo.f_tb(corpname) as 经营品牌 from tb
group by corpname
order by 公司代码
/*
公司代码 公司 经营品牌
1,2,3 化成公司 阿迪,耐克,奔驰
4 天意公司 宝马
5 长空公司 奥迪
*/看一下这个吧!你的意思和这个是一样的!我就不写了!
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-08 15:42:21
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Time] datetime,[Msg] varchar(5),[C4] varchar(6))
insert [tb]
select 1,'2009-1-1 10:10:11:10','1/3','中华' union all
select 1,'2009-1-1 10:10:11:20','2/3','人民' union all
select 1,'2009-1-1 10:10:11:30','3/3','共和国' union all
select 2,'2009-2-2 10:10:11:10','1/5','t1' union all
select 2,'2009-2-2 10:10:11:20','2/5','t2' union all
select 2,'2009-2-2 10:10:11:30','3/5','t3' union all
select 2,'2009-2-2 10:10:11:40','4/5','t4' union all
select 2,'2009-2-2 10:10:11:10','5/5','t5' union all
select 3,'2009-3-3 10:10:11:10','1/7','st1' union all
select 3,'2009-3-3 10:10:11:10','2/7','st2' union all
select 3,'2009-3-3 10:10:11:10','3/7','st3' union all
select 3,'2009-3-3 10:10:11:10','4/7','st4' union all
select 3,'2009-3-3 10:10:11:10','5/7','st5' union all
select 3,'2009-3-3 10:10:11:10','6/7','st6' union all
select 3,'2009-3-3 10:10:11:10','7/7','st7' union all
select 4,'2009-4-3 10:10:11:10','1','test1'
--------------开始查询--------------------------
select id, [C4]=stuff((select ''+[C4] from tb t where id=tb.id for xml path('')), 1, 0, '')+ltrim(count(1))
from tb
group by id
----------------结果----------------------------
/* id C4
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 中华人民共和国3
2 t1t2t3t4t55
3 st1st2st3st4st5st6st77
4 test11(4 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-08 15:42:21
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Time] datetime,[Msg] varchar(5),[C4] varchar(6))
insert [tb]
select 1,'2009-1-1 10:10:11:10','1/3','中华' union all
select 1,'2009-1-1 10:10:11:20','2/3','人民' union all
select 1,'2009-1-1 10:10:11:30','3/3','共和国' union all
select 2,'2009-2-2 10:10:11:10','1/5','t1' union all
select 2,'2009-2-2 10:10:11:20','2/5','t2' union all
select 2,'2009-2-2 10:10:11:30','3/5','t3' union all
select 2,'2009-2-2 10:10:11:40','4/5','t4' union all
select 2,'2009-2-2 10:10:11:10','5/5','t5' union all
select 3,'2009-3-3 10:10:11:10','1/7','st1' union all
select 3,'2009-3-3 10:10:11:10','2/7','st2' union all
select 3,'2009-3-3 10:10:11:10','3/7','st3' union all
select 3,'2009-3-3 10:10:11:10','4/7','st4' union all
select 3,'2009-3-3 10:10:11:10','5/7','st5' union all
select 3,'2009-3-3 10:10:11:10','6/7','st6' union all
select 3,'2009-3-3 10:10:11:10','7/7','st7' union all
select 4,'2009-4-3 10:10:11:10','1','test1'
--------------开始查询--------------------------
select id, [C4]=stuff((select ''+[C4] from tb t where id=tb.id for xml path('')), 1, 0, '')+'-'+ltrim(count(1))
from tb
group by id
----------------结果----------------------------
/* id C4
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 中华人民共和国-3
2 t1t2t3t4t5-5
3 st1st2st3st4st5st6st7-7
4 test1-1(4 行受影响)
*/
楼主看看这个就全明白了
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-08 16:59:00
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Time] datetime,[Msg] varchar(9))
insert [tb]
select 1,'2009-1-1 10:10:11:10','1/3中华' union all
select 1,'2009-1-1 10:10:11:20','2/3人民' union all
select 1,'2009-1-1 10:10:11:30','3/3共和国' union all
select 2,'2009-2-2 10:10:11:10','1/5t1' union all
select 2,'2009-2-2 10:10:11:20','2/5t2' union all
select 2,'2009-2-2 10:10:11:30','3/5t3' union all
select 2,'2009-2-2 10:10:11:40','4/5t4' union all
select 2,'2009-2-2 10:10:11:10','5/5t5' union all
select 3,'2009-3-3 10:10:11:10','1/7st1' union all
select 3,'2009-3-3 10:10:11:10','2/7st2' union all
select 3,'2009-3-3 10:10:11:10','3/7st3' union all
select 3,'2009-3-3 10:10:11:10','4/7st4' union all
select 3,'2009-3-3 10:10:11:10','5/7st5' union all
select 3,'2009-3-3 10:10:11:10','6/7st6' union all
select 3,'2009-3-3 10:10:11:10','7/7st7' union all
select 4,'2009-4-3 10:10:11:10','test1'
--------------开始查询--------------------------
select id, [msg]=stuff((select ''+right([msg],len(msg)-3) from tb t where id=tb.id for xml path('')), 1, 0, '')+'-'+ltrim(count(1))
from tb
group by id
----------------结果----------------------------
/* id msg
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 中华人民共和国-3
2 t1t2t3t4t5-5
3 st1st2st3st4st5st6st7-7
4 t1-1(4 行受影响)
*/
insert tb
select 1,'2009-1-1 10:10:11:10','1/3','中华' union all
select 1,'2009-1-1 10:10:11:20','2/3','人民' union all
select 1,'2009-1-1 10:10:11:30','3/3','共和国' union all
select 2,'2009-2-2 10:10:11:10','1/5','t1' union all
select 2,'2009-2-2 10:10:11:20','2/5','t2' union all
select 2,'2009-2-2 10:10:11:30','3/5','t3' union all
select 2,'2009-2-2 10:10:11:40','4/5','t4' union all
select 2,'2009-2-2 10:10:11:10','5/5','t5' union all
select 3,'2009-3-3 10:10:11:10','1/7','st1' union all
select 3,'2009-3-3 10:10:11:10','2/7','st2' union all
select 3,'2009-3-3 10:10:11:10','3/7','st3' union all
select 3,'2009-3-3 10:10:11:10','4/7','st4' union all
select 3,'2009-3-3 10:10:11:10','5/7','st5' union all
select 3,'2009-3-3 10:10:11:10','6/7','st6' union all
select 3,'2009-3-3 10:10:11:10','7/7','st7' select id, rt=(select top 1 time1 from tb where id=a.id), cr=(select C4+'' from tb where ID=a.id for xml path('')) from tb a group by id
结果:
1 2009-01-01 10:10:11.010 中华人民共和国
2 2009-02-02 10:10:11.010 t1t2t3t4t5
3 2009-03-03 10:10:11.010 st1st2st3st4st5st6st7