超难sql,求大牛解答

Question

id-name-price
1－销售1－40
2－销售1－30
3－销售1－20
4－销售1－10
5－销售2－90
6－销售2－32
7－销售2－20
8－销售2－10要得到price价格占比合计前50%的记录 如：销售1 price合计为10+20+30+40=100*50%=50，前50%占比的记录为
40+30=70>50
1－销售1－40
2－销售1－30销售2 price合计为10+20+32+90=152*50%=76，前占比50%的记录为
90>76
5－销售2－90

Answer 1

tb 你的表……
select t1.* from
tb t1,
(select  name,avg(price) avg_price from tb group by name) t2
where t1.name=t2.name 
and price>avg_price

Answer 2

不是酱紫滴
SELECT   t1.*
  FROM   tb t1,
         (  SELECT   MIN (id) id, name
              FROM   (SELECT   id,
                               name,
                               price,
                               (SUM (price) OVER (PARTITION BY name)) / 2
                                  percent,
                               SUM(price)
                                  OVER (
                                     PARTITION BY name
                                     ORDER BY id
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND     CURRENT ROW
                                  )
                                  subsum
                        FROM   tb)
             WHERE   subsum > percent
          GROUP BY   name) t2
 WHERE   t1.name = t2.name AND t1.id <= t2.id;

Answer 3

sorry 看错了
select t1.* from
tb t1,
(select  name,sum(price)/2 avg_price from tb group by name) t2
where t1.name=t2.name 
and price>avg_price

Answer 4

感谢2楼的，大牛给的提示，问题已完美解决，使用了oracle的统计分析函数 over（PARTITION BY ...)