select comid,y_month,m_month,cost_all,count_bad,comtroubledid ,
func(y_month,m_month,comid) as row_no
from t1
order by comid , y_month , m_month desc You have to create a function func to calculate the row_no and call it in your SQL
func(y_month,m_month,comid) as row_no
from t1
order by comid , y_month , m_month desc You have to create a function func to calculate the row_no and call it in your SQL
解决方案 »
- oracle报SP2-0460错误
- oracle数据库可以登录的上。在myeclipse中连接数据库时就一直报the network adapter could not establish con
- oracle中的update多表关联
- 那里可以下载到oracle 8.0.5 for nt啊!谢谢大家!
- 小弟用的是Oracle客户端。在SQL PLUS中输入的用户名称、主机字符串和口令都没问题,为什么一点“确定”就“未响应”啊?
- 如何在WIN 2000 Server上正确安装oracle9i数据库
- 求助:把当前时间写入表的date类型列中
- 关于导入存储过程的问题
- 定义游标类型问题
- 请问如何关闭当前数据库,启动另外一个数据库
- 请教如何安装Orack9i for Windows
- redhat linux 8.0, oracle启动后出现的问题,请指点
I think solving the problem is more important then writing a unreadable SQL :-)
rank() over (order by ...)
you can learn this from sql reference
(select rownum row_no, comid,y_month,m_month
from yourtable
group by rownum,comid,y_month,m_month
) a, yourtable b
where a.comid=b.comid and a.y_month=b.y_month and a.m_month=b.m_month
order by b.count_bad
( select rownum row_no,comid,y_month,m_month from
(select distinct comid,y_month,m_month from yourtable) c
)a , yourtable b
where a.comid=b.comid and a.y_month=b.y_month and a.m_month=b.m_month
order by b.count_bad
return number
as
cursor t_sor(v_y_month varchar2,v_m_month varchar2,v_comid varchar2) is
select rownum rm,a.* from t1 where comid=v_y_month and y_month=v_m_month and m_month=v_comid;
begin
for v_sor in t_sor(p_y_month ,p_m_month ,p_comid) loop
if v_sor.count_bad=p_count_bad then
return(v_sor.rm);
end if;
end loop;
end;
/select comid,y_month,m_month,cost_all,count_bad,comtroubledid ,
get(y_month,m_month,comid,count_bad) as row_no
from t1
order by comid , y_month , m_month,COUNT_BAD desc
完全可以利用分析函数去解决楼主的问题,不然,oracle为什么要设计出分析函数呢!
同意(西域浪子)的做法。
*
ERROR 位于第 1 行:
ORA-00439: 未启用特征: OLAP Window Functions不知道要怎么启用联机分析处理?
使用这个函数为什么要启用OLAP?有什么关系吗?
it's too slow to use func;