A few days ago, I replied to someone else's question: How to get the name of the designated person, and now recruit. First, test data:
SELECT A. * INTO #TMP_MARK FROM (SELECT ID = 1, MyName = 'Xiao Ming', mypass = 123 Union Select ID = 2, MyName = 'small flower', mypass = 122 union select id = 3, myname = 'small East , mypass = 111 union select id = 4, myname = 'calf', mypass = 122) A II. Press mypass arrangement, increment, and mypass value, the same number is listed, that is, the required results:
ID MyName Mypass Orderno 1 Xiao Ming 123 1 2 Little Flower 122 2 3 Maverick 122 2 4 Small East 111 3 Need to use a temporary table to generate sort, using the following statement:
Select Id, 1, 1) AS ORDERNO, MyPass Into #tmp_order from #tmp_mark group by mypass order by mypass desc - display result Select B. *, a.orderno from #tmp_order A, # TMP_MARK B Where A.MYPASS = B.MYPASS ORDER BY A.Orderno - If you separately know the small flower sort Select ORDERNO from #tmp_order a, #tmp_mark b Where a.mypass = B.MYPASS and B.MYNAME = 'small flower' Three, ranked by mypass, MYPASS value is listed in the same name, but the ranking is calculated, namely:
ID MYNAM MyPass ORDERNO 1 Xiao Ming 123 1 2 small flower 122 2 3 Maverick 122 2 4 small 111 4 This slightly simple, no need to temporary table:
Select a. *, myorder = (Select Count (*) from #tmp_mark where mypass> a.mypass 1 from #tmp_mark a order by myorder
