For example, give a class of students' scores, the same score of the same score 1: Create Table Test (F1 Char (10), F2 Char (10))
- Test Table
INSERT INTO TEST SELECT 'A' F1, '1' F2UNIONSELECT 'B' F1, '2' F2UNIONSETELECT 'C' F1, '3' F2UNIONSELECT 'D' F1, '3' F2UNISELECT 'E' F1, '4' F2UNIONSELECT ' F 'F1,' 4 'F2UNIONSELECT' G 'F1,' 4 'F2UNISELECT' H 'F1,' 7 'F2UNISELECT' I 'F1,' 9 'F2
- Insert data
Select ID = Identity (int, 0, 1), F1, F2 INTO #t from test order by f2 desc
SELECT A.F1, A.F2, A.ID 1-CAST (ID-CC-Minn As Char (10)) AS [Name] from #ta, (SELECT F2, CC, Minn from (SELECT F2, Count *) AS cc, min (id) -count (*) AS minn from #t group by f2) T) bwhere a.f2 = b.f2order by a.f2 DESC
--test
Drop Table #tdrop Table Test Method 2: - SQL Server Functions in Arrangement ------------- Set Quote_Identifier on Goset Ansi_Nulls On Go
Create Procedure Up_get_sort @ls_o varchar (10) assdeclare @LL_SORT INT, @LDC_Value Dec (9, 2), @LS_ID VARCHAR (16), @LL_SAME INT, @LS_SQL VARCHAR (200)
Begin
--- Rank calculation --------------------------------- There is such a rule value the same rankings, - and 3 people in place, for example, the first fraction, then the second place starts from 4 starts ------ ================== ==== ---------------- Before starting, save the keywords and values to the exam_use_sort table set @LL_SORT = 1
If LOWER (@ls_o) = 'DESC' or LOWER (@ls_o) = 'D' Begin - Descending Set @LS_SQL = 'SELECT ID, USE_VALUE FROM_USE_SORT ORDER BY ISNULL (USE_VALUE, 0) DESC'
Endelsebegin - Ascending Set @LS_SQL = 'SELECT ID, USE_VALUE FROM_USE_SORT ORDER BY ISNULL (Use_Value, 0)' End
EXEC ('Declare Cur Cursor for' @ ls_sql) Open Cur;
Fetch next from cur @ il @ ls_id, @ ldc_value;
WHILE @@ FETCH_STATUS = 0BEGINupdate exam_use_sort set use_sort = @ll_sort where use_value = @ldc_value; - have the same how many select @ll_same = isnull (count (*), 0) from exam_use_sortwhere use_value = @ldc_value;
Set @LL_SORT = @LL_SORT @LL_SAME
Fetch next from cur @ il @ ls_id, @ ldc_value;
DEAALLOCATE CUR;
End
Goset quoted_identifier off goset ANSI_NULLS ON Go
------- Function Usage ------------------ Create Table [Exam_Use_Sort] ([ID] [VARCHAR] (20) Collate Chinese_PRC_CI_AS NOT NULL, [USE_VALUE] [Decimal] (18, 2) NULL, [USE_SORT] [INT] NULL) ON [PRIMARY] GO (based on 9CBS)
