当前位置: 代码迷 >> 综合 >> MySQL如何实现类似ORACLE中rownum语法
  详细解决方案

MySQL如何实现类似ORACLE中rownum语法

热度:59   发布时间:2024-01-16 02:19:58.0
--mysql测试(10条数据,3个一组)

--建表
DROP TABLE T;
CREATE TABLE T(ID VARCHAR(32),VAL INT NOT NULL,CRT_DATE DATETIME NOT NULL);
SELECT * FROM T ORDER BY CRT_DATE;

--插入测试数据

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA3C0AD3E0530100007F9137', 1, NOW()+ INTERVAL 1 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA3D0AD3E0530100007F9137', 2, NOW()+ INTERVAL 2 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA3E0AD3E0530100007F9137', 3, NOW()+ INTERVAL 3 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA3F0AD3E0530100007F9137', 4, NOW()+ INTERVAL 4 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA400AD3E0530100007F9137', 5, NOW()+ INTERVAL 5 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA410AD3E0530100007F9137', 6, NOW()+ INTERVAL 6 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA420AD3E0530100007F9137', 7, NOW()+ INTERVAL 7 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA430AD3E0530100007F9137', 8, NOW()+ INTERVAL 8 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA440AD3E0530100007F9137', 9, NOW()+ INTERVAL 9 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA450AD3E0530100007F9137', 10, NOW()+ INTERVAL 10 HOUR);

insert into T (ID, VAL, CRT_DATE)
values ('6D8D0BE6BA460AD3E0530100007F9137', 11, NOW()+ INTERVAL 11 HOUR);

--语句
SELECT @ROWNUM := @ROWNUM + 1 RN, T.VAL
          FROM (SELECT @ROWNUM := 0) RN, T
         ORDER BY CRT_DATE DESC;
  相关解决方案