表: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语句怎么写?
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 开窗函数能解决。