<p>創(chuàng)建表：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://upload-images.jianshu.io/upload_images/9645324-1671da162c10e5fb.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>插入數(shù)據(jù)：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://upload-images.jianshu.io/upload_images/9645324-2b3e58aee9f2fc49.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>生成行號(hào)：
</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://upload-images.jianshu.io/upload_images/9645324-2b281de432af2d39.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>生成row_number效果1：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://upload-images.jianshu.io/upload_images/9645324-1a891fef11d90108.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>生成row_number效果2：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://upload-images.jianshu.io/upload_images/9645324-d6d1df1a4c8004a2.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>注意點(diǎn)：“</p><p>SELECT IF(@UID = T.ID AND @CID = T.PID,</p><p>? ? ? ? ? @RANK := @RANK + 1,</p><p>? ? ? ? ? @RANK := 1) AS RANK2,</p><p>”需要命名別名</p><p>附：代碼</p><blockquote><p/><p>DESC USERS;?</p><p>?</p><p>SELECT * FROM USERS;</p><p>-- 給USERS里的數(shù)據(jù)設(shè)置行號(hào), RN是行號(hào)</p><p>SELECT @ROWNUM := @ROWNUM + 1 AS RN, T.</p><p>? FROM (SELECT @ROWNUM := 0) R, USERS T</p><p/><p>SELECT IF(@UID = T.ID AND @CID = T.PID,</p><p>? ? ? ? ? @RANK := @RANK + 1,</p><p>? ? ? ? ? @RANK := 1) AS RANK2,</p><p>? ? ? ?T.,</p><p>? ? ? ?@UID := T.ID,</p><p>? ? ? ?@CID := T.PID</p><p class="image-package">? FROM (SELECT @UID := NULL, @CID := NULL, @RANK := 0) R,</p><p>? ? ? ?(SELECT ? PID, NAME, ID</p><p>? ? ? ? ? FROM USERS</p><p>? ? ? ? ?ORDER BY ?PID, NAME, ID) T;?</p><p> ?</p><p/><p>SELECT IF(@UID = T.PID,</p><p>? ? ? ? ? @RANK := @RANK + 1,</p><p>? ? ? ? ? @RANK := 1) AS RANK2,</p><p>? ? ? ?T.*,</p><p>? ? ? ?@UID := T.ID,</p><p>? ? ? ?@CID := T.PID</p><p>? FROM (SELECT @UID := NULL, @CID := NULL, @RANK := 0) R,</p><p>? ? ? ?(SELECT ? PID, NAME, ID</p><p>? ? ? ? ? FROM USERS</p><p>? ? ? ? ?ORDER BY ?PID, NAME, ID) T;?</p><p> ?</p><p/><p>?</p><p>
</p></blockquote><p>可以參考的其他類似處理方式：</p><blockquote><p>select *,@rank:=case when @current_id<>pid then 1 else @rank+1 end as rank2,</p><p>?@current_id:=id from users order by pid,name desc;</p><p>
</p><p>
</p><p>這段不是添寺，記錄：</p><p>?</p><p>SELECT?</p><p>ID,SUBSTRING_INDEX(GROUP_CONCAT(PID ORDER BY NAME DESC),',',1) PID?</p><p>FROM?</p><p>(</p><p>? ? SELECT</p><p>? ID,PID,NAME?</p><p>? ? FROM?</p><p>? ? USERS</p><p>? ? ORDER BY PID ASC ,NAME DESC</p><p>)B?</p><p>? ? GROUP BY ID</p><p> ;</p><p>
</p></blockquote>