bsp; 12795.060 .162401542
5763 13318.930 .169050772
5764 13295.187 .168749414
5765 26058.460 .330747499
********** ---------------- ----------
sum 78786.567 1
200407 5761 13710.265 .170545197
5762 13224.297 .164500127
5763 13710.265 .170545197
5764 13444.093 .167234221
5765 26301.881 .327175257
********** ---------------- ----------
sum 80390.801 1
200408 5761 14135.781 .170911147
5762 13376.468 .161730539
5763 14135.781 .170911147
5764 13929.694 .168419416
5765 27130.638 .328027751
********** ---------------- ----------
sum 82708.362 1
20 rows selected.
6 first,last函数使用介绍
Quote:
取出每月通话费最高和最低的两个用户.
1 select bill_month,area_code,sum(local_fare) local_fare,
2 first_value(area_code)
3 over (order by sum(local_fare) desc
4 rows unbounded preceding) firstval,
5 first_value(area_code)
6 over (order by sum(local_fare) asc
7 rows unbounded preceding) lastval
8 from t
9 group by bill_month,area_code
10* order by bill_month
SQL> /
BILL_MONTH AREA_CODE LOCAL_FARE FIRSTVAL LASTVAL
---------- --------- ---------------- --------------- ---------------
200405 5764 12487.791 5765 5764
200405 5762 12643.791 5765 5764
200405 5761 13060.433 5765 5764
200405 5765 25057.736 5765 5764
200405 5763 13060.433 5765 5764
200406 5762 12795.060 5765 5764
200406 5763 13318.930 5765 5764
200406 5764 13295.187 5765 5764
200406 5765 26058.460 5765 5764
200406 5761 13318.930 5765 5764
200407 5762 13224.297 5765 5764
200407 5765 26301.881 5765 5764
200407 5761 13710.265 5765 5764
200407 5763 13710.265 5765 5764
200407 5764 13444.093 5765 5764
200408 5762 13376.468 5765 5764
200408 5764 13929.694 5765 5764
200408 5761 14135.781 5765 5764
200408 5765 27130.638 5765 5764
200408 5763 14135.781 5765 5764
20 rows selected.
上一页 [1] [2] [3] [4] |
