Create Database Test1gouse Test1Gocreate 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
