表结构都没?本SQL纯属虚构,若有雷人,实属天灾select col1,col2,....,coln from (select col1,col2,....,coln,dense_rank(partition by trunc(tran_date) order by tran_date) dr from table_name) where dr<=20;
SELECT TOP 20 * FROM 表名;
select * from table where 条件1 and rownum<=20
select col1,col2,col3 from ( select col1,col2,col3,row_number()over(partition by datecol order by 1) rn from tab ) where rn<=20
SELECT TOP 20 * FROM 表名 WHRER TO_CHAR(DATE表示时间的列) = TO_CHAR(sysDate);
没有表结构啊, 试一试这个:select * from table where 条件1 and rownum<=20
select * from (select VOTEID ,mp,BRAND,TOPICID ,MEMO ,DBTIME ,dense_rank(partition by trunc(DBTIME) order by tran_date) dr from TAB_TOPICMP ) where dr<=202楼和4楼都 可以
---try it select tt.* from (select t.*, row_number()over(partition by trunc(DBTIME, 'dd') order by DBTIME desc) rn from TAB_TOPICMP t) tt where tt.rn <= 20
SELECT COL1, COL2, COL3 FROM (SELECT COL1, COL2, COL3, ROW_NUMBER() OVER ORDER BY 1) RN FROM TAB) WHERE RN <= 20
(select col1,col2,....,coln,dense_rank(partition by trunc(tran_date) order by tran_date) dr from table_name)
where dr<=20;
select * from table where 条件1 and rownum<=20
select col1,col2,col3 from (
select col1,col2,col3,row_number()over(partition by datecol order by 1) rn from tab
)
where rn<=20
试一试这个:select * from table where 条件1 and rownum<=20
表结构如下:
create table TAB_TOPICMP
(
VOTEID VARCHAR2(32) not null,--sessionid
MP VARCHAR2(100),--手机号
BRAND VARCHAR2(100),--品牌
TOPICID VARCHAR2(32) not null,
VOTETIME DATE,--提交时间(这个无效,不需要)
MEMO VARCHAR2(4000),--记录
DBTIME DATE--真实提交时间(按照这个来统计)
)其中每天都有记录,我想一次性全部查出来每天的前20条数据。
2L的我试了下,不行啊,报错。
(select VOTEID ,mp,BRAND,TOPICID ,MEMO ,DBTIME ,dense_rank(partition by trunc(DBTIME) order by tran_date) dr
from TAB_TOPICMP
)
where dr<=202楼和4楼都 可以
select tt.*
from (select t.*,
row_number()over(partition by trunc(DBTIME, 'dd') order by DBTIME desc) rn
from TAB_TOPICMP t) tt
where tt.rn <= 20
FROM (SELECT COL1,
COL2,
COL3,
ROW_NUMBER() OVER ORDER BY 1) RN
FROM TAB)
WHERE RN <= 20