select ID,to_char(ADate,'yyyy-mm-dd'),Name from A where (to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31') and rownum <= 3
select * from (select ID,to_char(ADate,'yyyy-mm-dd'),Name,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31') where rm<3;
呵呵... Oracle 星光闪闪... 楼主的问题应该没问题啦~
to beckhambobo(beckham): 你的很好用,想再问一下,在此基础上还有什么办法可以知道哪天的超出了3条记录,即大于3条,或者在大于3条后,在此日期下出现一条数据,其name值为……,能做到吗?
select * from (select ID,to_char(ADate,'yyyy-mm-dd'),Name,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31') where rm<3; union select * from (select ID,to_char(ADate,'yyyy-mm-dd'), '...' ,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31') where rm=3;
from A
where (to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31') and rownum <= 3
(select ID,to_char(ADate,'yyyy-mm-dd'),Name,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31')
where rm<3;
楼主的问题应该没问题啦~
你的很好用,想再问一下,在此基础上还有什么办法可以知道哪天的超出了3条记录,即大于3条,或者在大于3条后,在此日期下出现一条数据,其name值为……,能做到吗?
(select ID,to_char(ADate,'yyyy-mm-dd'),Name,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31')
where rm<3;
union
select * from
(select ID,to_char(ADate,'yyyy-mm-dd'), '...' ,row_number() over(partition by trunc(ADate) order by rownum) rm from A where to_char(ADate,'yyyy-mm-dd') between '2003-10-01' and '2003-10-31')
where rm=3;
完全符合,谢谢!