请教一个问题,为什么排序完之后再取rownum还是不正常?用row_number() over ()也一样
数据库为Oracle10g
下面两段代码取出的数据的顺序就不一样.请教原因.
测试语句:
create table TM
(
TM_ID VARCHAR2(32) not null,
TM_GROUP_ID VARCHAR2(32),
TM_CONTENTVARCHAR2(500) not null,
TM_TYPE_SET VARCHAR2(20),
TM_TIP_TYPE NUMBER(1),
TM_ORDERNUMBER(2)
);
----------------------------------------------------------------------------------
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('E7F84088F2284C92BCB366550DC41031', 'GROUP22012736453792030', '11111111111111', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('B3A9C761C7BE4036A15EEF0ABC02CD17', 'GROUP22012736453792030', 'INTERNETBBBB22222', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('7D319925FFF14C9EA179E56D29AFA969', 'GROUP22012736453792030', 'MMSBBBB', '201', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('97368130B04B467F96FAA8D0EEBA86F3', 'GROUP22012736453792030', 'MMSAA', '201', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('DC10E8F040954096B1D7CA133C010D1E', 'GROUP22012736453792030', 'ffffffffff55555', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('357A380943FB47E2BF5D8622EDC5EF23', 'GROUP22012736453792030', 'sun1', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('417E694D9ED849F19B99A1CB569075DD', 'GROUP22012736453792030', 'sun2', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('C546B8C43016492CB71DA87C5B8AE771', 'GROUP22012736453792030', 'sun4', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('03FCD815DA044061A81146FCC82DC10E', 'GROUP22012736453792030', '', '201', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('69210E20A7B64BC9B8FF77397008C4E4', 'GROUP22012736453792030', 'jkkkkjhhjjh', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('773DA9EA65464A3D8A53BCF26FD5AB99', 'GROUP22012736453792030', 'hhhhhhhhhhhh', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('8D53C66CA9CB41CA81DB121E7668B40D', 'GROUP22012736453792030', 'dsunikmk', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('754730ED7B294C26B372444E87D4A6C6', 'GROUP22012736453792030', 'sun5', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('2C1D35D4426C47FEA188681346F8AB14', 'GROUP22012736453792030', 'sun6', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('11F138C6B3DF4C9B922772F1997F6462', 'GROUP22012736453792030', 'sun3', '4000', 0, 0);
insert into TM (TM_ID, TM_GROUP_ID, TM_CONTENT, TM_TYPE_SET, TM_TIP_TYPE, TM_ORDER)
values ('726B3765F73C43F69355F5C2A48F15EB', 'GROUP22012736453792030', 'ddddd', '4000', 0, 0);
commit;
-------------------------------------------------------------------------------
select * from (
select row_number() over (order by TM_TYPE_SET,TM_TIP_TYPE,TM_ORDER) no, tt.* from TM tt
)
where no 复制代码
|
