select * from (select rownum rn,* from music) t where t.rn<11 order by classid DESC
select * from (select rownum rn,* from music) t where t.rn<11 order by classid DESCt和rn是什么啊?
我觉得是分页那出了问题 select mus_id,mus_name,link,class_id,is_date from music where rownum<".$endset." minus select mus_id,mus_name,link,class_id,is_date from music where rownum<".$offset." ORDER BY id DESC 他只是排了最后一句where rownum<".$offset." 的id如果select mus_id,mus_name,link,class_id,is_date from music ORDER BY id DESC 这样写,就是可以排序了,可是就没发分页了
如果想先排序然后再分页,只能用上面的语句 解释一下: select * from ( select rownum id,t.* from ( select * from music where ORDER BY classid DESC --排序 ) t where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围 ) tt where tt.id>10; --取出符合条件的数据
t是什么啊? 你有QQ吗?我能加你吗?
t只是一个别名而已, select * from music where ORDER BY classid DESC 这个语句取出的数据集看作为一个表(t)我有qq,但很少上,现在也没有qq用:)
t 是什么?? select rownum id,t.* from ( select * from music where ORDER BY classid DESC --排序 ) t // 给嵌套查询起的别名啊!!select rownum id,t.* from ( select * from music where ORDER BY classid DESC --排序 ) t where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围 ) tt // 查询出 首 20条记录啊!!select * from ( select rownum id,t.* from ( select * from music where ORDER BY classid DESC --排序 ) t where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围 ) tt where tt.id>10; --取出符合条件的数据 // 查询出 首 20条记录里面 ID>10 的记录啊!!
多了一个where select * from (select rownum id,t.* from (select * from music ORDER BY classid DESC) t where rownum<20) tt where tt.id>10;测试情况: SQL> select * from t_dtime order by c_time desc;C_TIME ------------------- 2003-09-08 16:48:02 2003-09-08 16:47:01 2003-09-08 16:46:01 2003-09-08 16:45:08 2003-09-08 16:44:06 2003-09-08 16:43:05 2003-09-08 16:42:04 2003-09-08 16:41:02 2003-09-08 16:40:01 2003-09-08 16:39:01 2003-09-08 16:38:08C_TIME ------------------- 2003-09-08 16:37:06 2003-09-08 16:36:05 2003-09-08 16:35:03 2003-09-08 16:34:02 2003-09-08 16:33:01 2003-09-08 16:32:09 2003-09-08 16:31:08 2003-09-08 16:30:06 2003-09-08 16:29:05 2003-09-08 16:28:03 2003-09-08 16:27:02C_TIME ------------------- 2003-09-08 16:26:01 2003-09-08 16:25:09 2003-09-08 16:24:07 2003-09-08 16:23:06 2003-09-08 16:22:05 2003-09-08 16:21:03 2003-09-08 16:20:02 2003-09-08 16:19:01 2003-09-08 16:18:09 2003-09-08 16:17:07 2003-09-08 16:16:06C_TIME ------------------- 2003-09-08 16:15:04 2003-09-08 16:14:03 2003-09-08 16:13:02 2003-09-08 16:12:01 2003-09-08 16:11:09 2003-09-08 16:10:07 2003-09-08 16:09:47已选择40行。SQL> select * from ( 2 select rownum id,t.* from ( 3 select * from t_dtime ORDER BY c_time DESC 4 ) 5 t 6 where rownum<20 7 ) tt where tt.id>10; ID C_TIME ---------- ------------------- 11 2003-09-08 16:38:08 12 2003-09-08 16:37:06 13 2003-09-08 16:36:05 14 2003-09-08 16:35:03 15 2003-09-08 16:34:02 16 2003-09-08 16:33:01 17 2003-09-08 16:32:09 18 2003-09-08 16:31:08 19 2003-09-08 16:30:06已选择9行。SQL>
(select rownum rn,* from music) t
where t.rn<11
order by classid DESC
select mus_id,mus_name,link,class_id,is_date from music where rownum<".$endset." minus select mus_id,mus_name,link,class_id,is_date from music where rownum<".$offset." ORDER BY id DESC
他只是排了最后一句where rownum<".$offset." 的id如果select mus_id,mus_name,link,class_id,is_date from music ORDER BY id DESC
这样写,就是可以排序了,可是就没发分页了
解释一下:
select * from (
select rownum id,t.* from (
select * from music where ORDER BY classid DESC --排序
)
t
where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围
) tt where tt.id>10; --取出符合条件的数据
你有QQ吗?我能加你吗?
select * from music where ORDER BY classid DESC 这个语句取出的数据集看作为一个表(t)我有qq,但很少上,现在也没有qq用:)
rownum id这个是什么啊?
rownum id = rownum as id //列的别名
select rownum id,t.* from (
select * from music where ORDER BY classid DESC --排序
) t
// 给嵌套查询起的别名啊!!select rownum id,t.* from (
select * from music where ORDER BY classid DESC --排序
)
t
where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围
) tt
// 查询出 首 20条记录啊!!select * from (
select rownum id,t.* from (
select * from music where ORDER BY classid DESC --排序
)
t
where rownum<20 --对排序后的数据加上rownum 兵缩小数据集的范围
) tt where tt.id>10; --取出符合条件的数据
// 查询出 首 20条记录里面 ID>10 的记录啊!!
select * from (select rownum id,t.* from (select * from music ORDER BY classid DESC) t
where rownum<20) tt where tt.id>10;测试情况:
SQL> select * from t_dtime order by c_time desc;C_TIME
-------------------
2003-09-08 16:48:02
2003-09-08 16:47:01
2003-09-08 16:46:01
2003-09-08 16:45:08
2003-09-08 16:44:06
2003-09-08 16:43:05
2003-09-08 16:42:04
2003-09-08 16:41:02
2003-09-08 16:40:01
2003-09-08 16:39:01
2003-09-08 16:38:08C_TIME
-------------------
2003-09-08 16:37:06
2003-09-08 16:36:05
2003-09-08 16:35:03
2003-09-08 16:34:02
2003-09-08 16:33:01
2003-09-08 16:32:09
2003-09-08 16:31:08
2003-09-08 16:30:06
2003-09-08 16:29:05
2003-09-08 16:28:03
2003-09-08 16:27:02C_TIME
-------------------
2003-09-08 16:26:01
2003-09-08 16:25:09
2003-09-08 16:24:07
2003-09-08 16:23:06
2003-09-08 16:22:05
2003-09-08 16:21:03
2003-09-08 16:20:02
2003-09-08 16:19:01
2003-09-08 16:18:09
2003-09-08 16:17:07
2003-09-08 16:16:06C_TIME
-------------------
2003-09-08 16:15:04
2003-09-08 16:14:03
2003-09-08 16:13:02
2003-09-08 16:12:01
2003-09-08 16:11:09
2003-09-08 16:10:07
2003-09-08 16:09:47已选择40行。SQL> select * from (
2 select rownum id,t.* from (
3 select * from t_dtime ORDER BY c_time DESC
4 )
5 t
6 where rownum<20
7 ) tt where tt.id>10; ID C_TIME
---------- -------------------
11 2003-09-08 16:38:08
12 2003-09-08 16:37:06
13 2003-09-08 16:36:05
14 2003-09-08 16:35:03
15 2003-09-08 16:34:02
16 2003-09-08 16:33:01
17 2003-09-08 16:32:09
18 2003-09-08 16:31:08
19 2003-09-08 16:30:06已选择9行。SQL>