Sort data by Sort definition table

Sort | data
/*--Original post address: http://community.csdn.net/expert/topic/3866/3866872.xml?temp=.2154199--*/

--Test Data CREATE TABLE TB1 (ID int,col1 varchar (a), col2 int) Insert TB1 Select 1, ' AA ', 111union all select 2, ' AA ', 111union all Select 3, ' AA ', 111union all select 4, ' BB ', 222union all select 5, ' BB ', 222 union ALL Select 6, ' CC ', 333 UNION ALL Selec T 7, ' CC ', 333 union ALL Select 8, ' cc ', 333union all Select 9, ' CC ', 333

CREATE TABLE TB2 (col1 varchar (), size int) Insert TB2 select ' AA ', 2union all select ' CC ', 2--Change Order UNION ALL select ' BB ', 0go

/*--Processing Requirements:

TB1 is associated with TB2 through col2, TB2 col1 contains all tb1.col1

Write functions are required to perform the following sort: The col1 in TB1 is arranged according to the number of records defined in Col1 in TB2. For example: in TB2: AA of the size=2, on the first row 2 AA records cc size=2, and after AA, so then row 2 cc Records and BB in TB2 definition is 0, then do not participate in the sort, always show in the last repeat cycle, not enough by the back of the record to fill up

The results of the following sort are achieved:

id          col1       col2        --------------------------------1            aa         1112            aa         1116            cc         3337            cc         3333            aa         1118            cc         3339            cc         3334           bb         2225            bb         222--*/

--Sort functions Create function f_sort (@id int, @col1 varchar) returns varchar Asbegin declare @i int, @size int,@j int set @i=0 Select @i=case when @size are null then @i+1 else @i end @size =case when @col1 =col1 the size else @size end from then if @size =0 return (replicate, ' 9 ") Select @j=count (*) from TB1 where id< @id and col1= @col1 return (right (10000000000+is Null (@j,0)/@size, Ten) +right (10000000000+@i,10)) Endgo

--Invoke Implementation query select * FROM Tb1 aorder by Dbo.f_sort (id,col1) go

--Delete test drop table Tb1,tb2drop function F_sort