表ANUM_i NAME
------------------------------
40 a
30 b
10 c
10 d
5 e
5 f
求一个语句
取这么几列:这几列的sum(NUM_i)超过此表sum(NUM_i)的50% 并且这几列是该表中NUM_i最大的几列
效果是:
NUM_i NAME
------------------------------
40 a
30 b
------------------------------
40 a
30 b
10 c
10 d
5 e
5 f
求一个语句
取这么几列:这几列的sum(NUM_i)超过此表sum(NUM_i)的50% 并且这几列是该表中NUM_i最大的几列
效果是:
NUM_i NAME
------------------------------
40 a
30 b
表ANUM_i NAME
------------------------------
40 a
30 b
10 c
10 d
5 e
5 f
求一个语句
取这么几列:这几列的sum(NUM_i)不超过此表sum(NUM_i)的50% 并且这几列是该表中NUM_i最大的几列
效果是:
NUM_i NAME
------------------------------
40 a
若设置为80
则要查出:
NUM_i NAME
------------------------------
40 a
30 b
2 SELECT 40 num_i,'a' num_name FROM DUAL UNION ALL
3 SELECT 30 num_i,'b' num_name FROM DUAL UNION ALL
4 SELECT 10 num_i,'c' num_name FROM DUAL UNION ALL
5 SELECT 10 num_i,'d' num_name FROM DUAL UNION ALL
6 SELECT 5 num_i,'e' num_name FROM DUAL UNION ALL
7 SELECT 5 num_i,'f' num_name FROM DUAL
8 )
9 SELECT m.num_i,m.num_name
10 FROM (SELECT t.*,
11 SUM(t.num_i) OVER(ORDER BY num_i DESC) - SUM(t.num_i) OVER() / 2 snum
12 FROM t) m
13 WHERE m.snum <= 0
14 ; NUM_I NUM_NAME
---------- --------
40 a
http://topic.csdn.net/u/20110328/18/e1c4b15f-9b98-4269-9b30-85fd45b60970.html不如参考看看