Sort data according to sort definition table
Test data: Create Table TB1 (ID INT, Col1 Varchar (10), Col2 Int) Insert TB1 Select 1, 'AA', 111UNION All SELECT 3, 'AA', 111UNION All SELECT 4 , 'BB', 222UNION All SELECT 5, 'BB', 222UNION All SELECT 6, 'CC', 333Union All Select 7, 'CC', 333Union All Select 8, 'CC', 333Union All Slect 9, 'CC', 333CREATE TABLE TB2 (Col1 Varchar (10), Size Int) INSERT TB2 SELECT 'AA', 2UNION All Select 'CC', 2UNION All Select 'BB', 0GO
Description: TB1 and TB2 are associated with TB2, and the COL1 of TB2 contains all TB1.COL1. The write function is required: COL1 in TB1 is arranged in accordance with the number of records defined in TB2. For example: in TB2: aa's size = 2, the first row 2 AA records CC size = 2, and after AA, the record of the 2 CC is then defined in TB2, no Participate in sorting, always displayed in the final repeating cycle, not enough by the following records, finally achieve the following order results: ID col1 col2 -------------------- - --------- 1 AA 1112 AA 1116 CC 3337 CC 3333 AA 1118 CC 3339 CC 3334 BB 2225 BB 222 - * /
- Sort Function Create Function F_SORT (@ID INT, @ col1 varchar (10)) Returns Varchar (20) asbegin declare @i int, @ size int, @ J int set @ i = 0 Select @ i = Case When @Size Is Null Then @ i 1 else @i end, @ size = case when @ col1 = col1 the size else @Size end from tb2 if @ size = 0 return (Replicate (20, '9')) Select @ J = Count (*) From TB1 WHERE ID <@ID and return (Right (10000000000 ISNULL (@ J, 0) / @ size, 10) Right (10000000000 @ i, 10)) EndGo - Call implementation Query SELECT * FROM TB1 Aorder by dbo.f_sort (id, col1) Go - Delete Test DROP TABLE TB1, TB2DROP FUNCTION F_SORT Original Address
