select op_cdr.caller,service_result.result_type,service_result.object_id,count(service_result.service_result_id) c
from service_result,op_cdr
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc;上面的SQL语句,是对主叫进行不同服务的查询次数的统计。
数据里面有许多不同的caller主叫,对不同的服务有不同的查询次数,通常都有大于10条以上的查询记录。
现在对应列caller主叫相同的记录,按列count(service_result.service_result_id) c 查询次数的降序,取前10条。
请问该如何改写上面的SQL语句?
from service_result,op_cdr
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc;上面的SQL语句,是对主叫进行不同服务的查询次数的统计。
数据里面有许多不同的caller主叫,对不同的服务有不同的查询次数,通常都有大于10条以上的查询记录。
现在对应列caller主叫相同的记录,按列count(service_result.service_result_id) c 查询次数的降序,取前10条。
请问该如何改写上面的SQL语句?
from service_result,op_cdr,rownum as rn
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc;)
select * from result where rn<11;
from service_result,op_cdr
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc;)
select * from result where rn<11;
from service_result,op_cdr
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc;)
select * from result where rn<11;不好意思
写错两遍
^_^
from service_result,op_cdr
where service_result.op_cdr_id = op_cdr.op_cdr_id
group by op_cdr.caller,service_result.result_type,service_result.object_id
order by caller,c desc)
select * from result where rn<11;再来一遍
,给你一个例子SQL> SELECT * FROM TBL1; ID NAME
---------- --------------------
1 A
1 B
1 C
2 A
2 B
2 C
3 D
4 E8 rows selected.
SQL> SELECT * FROM (
2 SELECT ID,NAME,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) ORD FROM TBL1
3 ) A
4 WHERE A.ORD<=2; ID NAME ORD
---------- -------------------- ----------
1 A 1
1 B 2
2 A 1
2 B 2
3 D 1
4 E 16 rows selected.