mysql 实现row_number() over(partit

<p>创建表：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/1671da162c10e5fb.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>插入数据：</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/2b3e58aee9f2fc49.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><p>生成行号：
</p><blockquote><p class="image-package"><img class="uploaded-img" src="https://img.haomeiwen.com/i9645324/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://img.haomeiwen.com/i9645324/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://img.haomeiwen.com/i9645324/d6d1df1a4c8004a2.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" width="auto" height="auto"/></p><p>
</p></blockquote><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>”需要命名别名</p><p>附：代码</p><blockquote><p/><p>DESC USERS; </p><p> </p><p>SELECT * FROM USERS;</p><p>-- 给USERS里的数据设置行号, RN是行号</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>