一个分组排序的问题
我现在有一个表
字段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
