我现在有一个表
字段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
字段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
ORDER BY 的字段只能是 GROUP BY 里面的
所以不能按sort分组同时按id排序
该怎么做呢
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
endselect distinct dbo.fun_test(sort) , sort from 表
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 aif 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
goselect sname = dbo.fn_info1(sort),sort from a group by sort order by sort descdrop table a
(影響 4 個資料列)iID sname sort
----------- ----- ----
1 a s1
2 b s2
3 c s1
4 d s2(影響 4 個資料列)sname sort
---------- ----
b,d s2
a,c s1(影響 2 個資料列)