创建表：
插入数据：
生成行号：
生成row_number效果1：
生成row_number效果2：
注意点：“
SELECT IF(@UID = T.ID AND @CID = T.PID,
@RANK := @RANK + 1,
@RANK := 1) AS RANK2,
”需要命名别名
附：代码
DESC USERS;
SELECT * FROM USERS;
-- 给USERS里的数据设置行号, RN是行号
SELECT @ROWNUM := @ROWNUM + 1 AS RN, T.
FROM (SELECT @ROWNUM := 0) R, USERS T
SELECT IF(@UID = T.ID AND @CID = T.PID,
@RANK := @RANK + 1,
@RANK := 1) AS RANK2,
T.,
@UID := T.ID,
@CID := T.PID
FROM (SELECT @UID := NULL, @CID := NULL, @RANK := 0) R,
(SELECT PID, NAME, ID
FROM USERS
ORDER BY PID, NAME, ID) T;
SELECT IF(@UID = T.PID,
@RANK := @RANK + 1,
@RANK := 1) AS RANK2,
T.*,
@UID := T.ID,
@CID := T.PID
FROM (SELECT @UID := NULL, @CID := NULL, @RANK := 0) R,
(SELECT PID, NAME, ID
FROM USERS
ORDER BY PID, NAME, ID) T;
可以参考的其他类似处理方式：
select *,@rank:=case when @current_id<>pid then 1 else @rank+1 end as rank2,
@current_id:=id from users order by pid,name desc;
这段不是，记录：
SELECT
ID,SUBSTRING_INDEX(GROUP_CONCAT(PID ORDER BY NAME DESC),',',1) PID
FROM
(
SELECT
ID,PID,NAME
FROM
USERS
ORDER BY PID ASC ,NAME DESC
)B
GROUP BY ID
;
|
|
转播
