一个分组排序的问题
我现在有一个表
字段id   name   sort

现在想得到一个表
按id倒序排序
并且按sort来分类

就是说同一sort的取name一起显示
但排序要按id循序
id是自动递增的

例如
id   name   sort
1     a         s1
2     b         s2
3     c         s1
3     d         s2

我想得到的是
name   sort
b,d     s2
a,c     s1




------解决方案--------------------

create function fun_test(@cid varchar(20))
returns varchar(2000)
as
begin
declare @chr varchar(2000)
set @chr= ' '
select @chr=@chr+c+ ', ' from 表 where sort=@cid order by id
set @chr=left(@chr,len(@chr)-1)
return @chr
end

select distinct dbo.fun_test(sort) , sort from 表
------解决方案--------------------
create table a(iID int,sname varchar(1),sort varchar(2))
insert a
select 1, 'a ', 's1 '
union all select 2, 'b ', 's2 '
union all select 3, 'c ', 's1 '
union all select 4, 'd ', 's2 '
select * from a

if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[fn_info1] ') and xtype in (N 'FN ', N 'IF ', N 'TF '))
drop function [dbo].[fn_info1]
go
create function dbo.fn_info1(@sort varchar(2))
returns varchar(10)
as
begin
declare @name varchar(10)
set @name = ' '
select @name = @name + ', ' + sname from a where sort = @sort
if @name <> ' '
select @name = stuff(@name,1,1, ' ')
return @name
end
go

select sname = dbo.fn_info1(sort),sort from a group by sort order by sort desc

drop table a