表:create table M_UserLogin(
id NUMBER PRIMARY KEY,
timepro number,
monName varchar2(20),
opmontime date,
Properties varchar2(10),
userApp varchar2(30),
userTime date,
CMSID number,
compID varchar2(30)
);
序列:create sequence S_UserLogin
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
查询语句:select * from( select compID,count(compID) as count1 from M_UserLogin where to_char(userTime,'dd')=29 and to_char(userTime,'mm')=6 group by compID order by count1 desc) where rownum=1;
这个只能查询出一条数据。如果有n条数据一样的话,SQL语句怎么写?
id NUMBER PRIMARY KEY,
timepro number,
monName varchar2(20),
opmontime date,
Properties varchar2(10),
userApp varchar2(30),
userTime date,
CMSID number,
compID varchar2(30)
);
序列:create sequence S_UserLogin
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
查询语句:select * from( select compID,count(compID) as count1 from M_UserLogin where to_char(userTime,'dd')=29 and to_char(userTime,'mm')=6 group by compID order by count1 desc) where rownum=1;
这个只能查询出一条数据。如果有n条数据一样的话,SQL语句怎么写?
解决方案 »
- 复合索引与单独索引优先级,以及rebuild索引的存储过程
- Sequence重复问题如何解决
- 救命啊,如何将dmp导入数据库啊!
- -- 问 -- 低版本客户端访问高版本数据库问题
- 写oracle的java sources,用到其他的jar 包?该怎么办?
- 在数据库查询中,如何处理空格和回车
- TABLE的創建問題?
- OCCI有for c++ Builder的LIB吗?注意不是OCI
- 用imp导入记录时,如果目的表存在主键相同但是其它字段内容不同的记录,如果此时我希望用源记录覆盖目的记录,请教参数是什么、imp命令该
- 为什么我的OMS打开没有数据库呢???
- 求一存储过程
- C语言显示oracle数据表中的utf8字符串乱码
from (select compID, count(compID) as count1
from M_UserLogin
where to_char(userTime, 'dd') = 29
and to_char(userTime, 'mm') = 6
group by compID
order by count1 desc)
where rownum < n;
from (select compID,
count(compID) as count1,
row_number() over(partition by compID order by count(compID) desc) grouprank
from M_UserLogin
where to_char(userTime, 'dd') = 29
and to_char(userTime, 'mm') = 6)
where grouprank = 2;oracle over 开窗函数能解决。