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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.