网上找个通用的函数,datatalbe或者dataset转json的函数。我也问问。如果要得到: Initial name1 name2 ---------- ---------- ---------- T Tom 天天 Z 张三 在或者Initial nameall ---------- ---------- T Tom,天天 Z 张三,在该怎么做呢?
with cte as ( select cast('{'+a.name+'}' as varchar(100)) as name,a.initial,a.id from #person a where not exists(select 1 from #person x where x.initial=a.initial and x.id<a.id) union all select cast(b.name+',{'+a.name+'}' as varchar(100)),a.initial,a.id from #person a inner join cte b on a.initial=b.initial and a.id>b.id where not exists(select 1 from #person x where x.initial=a.initial and x.id>b.id and x.id<a.id) ) select a.initial+':['+a.name+']',a.id from cte a where exists( select 1 from (select initial,max(id) as id from cte group by initial) x where x.id=a.id ) 此方法应该效率可以
create table #person(name varchar(10),initial varchar(20),id int identity(1,1)) insert into #person(name,initial) select 'Tom','T' union all select '张三','Z'union all select '在','Z'union all select '天天','T' union all select '年年','T' union all select '吃饭','Z' select * from #personwith cte as ( select cast('{'+a.name+'}' as varchar(100)) as name,a.initial,a.id from #person a where not exists(select 1 from #person x where x.initial=a.initial and x.id<a.id) union all select cast(b.name+',{'+a.name+'}' as varchar(100)),a.initial,a.id from #person a inner join cte b on a.initial=b.initial and a.id>b.id where not exists(select 1 from #person x where x.initial=a.initial and x.id>b.id and x.id<a.id) ) select a.initial+':['+a.name+']',a.id from cte a where exists( select 1 from (select initial,max(id) as id from cte group by initial) x where x.id=a.id )
在数据库里面做效率低。。都是在程序里面实现的。看看这里
http://www.cnblogs.com/jyshis/archive/2011/09/07/2169452.html
需要看lz的 开发程序使用的语言了。 基本上现在的主流语言都支持直接转json数据格式。
.net java 都ok的 。 参考 http://json.codeplex.com
Initial name1 name2
---------- ---------- ----------
T Tom 天天
Z 张三 在或者Initial nameall
---------- ----------
T Tom,天天
Z 张三,在该怎么做呢?
(
select cast('{'+a.name+'}' as varchar(100)) as name,a.initial,a.id from #person a where
not exists(select 1 from #person x where x.initial=a.initial and x.id<a.id)
union all
select cast(b.name+',{'+a.name+'}' as varchar(100)),a.initial,a.id from #person a
inner join cte b on a.initial=b.initial and a.id>b.id
where not exists(select 1 from #person x where x.initial=a.initial and x.id>b.id and x.id<a.id)
)
select a.initial+':['+a.name+']',a.id from cte a where exists(
select 1 from (select initial,max(id) as id
from cte group by initial) x where x.id=a.id )
此方法应该效率可以
create table #person(name varchar(10),initial varchar(20),id int identity(1,1))
insert into #person(name,initial)
select 'Tom','T' union all
select '张三','Z'union all
select '在','Z'union all
select '天天','T' union all
select '年年','T' union all
select '吃饭','Z'
select * from #personwith cte as
(
select cast('{'+a.name+'}' as varchar(100)) as name,a.initial,a.id from #person a where
not exists(select 1 from #person x where x.initial=a.initial and x.id<a.id)
union all
select cast(b.name+',{'+a.name+'}' as varchar(100)),a.initial,a.id from #person a
inner join cte b on a.initial=b.initial and a.id>b.id
where not exists(select 1 from #person x where x.initial=a.initial and x.id>b.id and x.id<a.id)
)
select a.initial+':['+a.name+']',a.id from cte a where exists(
select 1 from (select initial,max(id) as id
from cte group by initial) x where x.id=a.id )