超难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  name,sum(price)/2 avg_price from tb group by name) t2
where t1.name=t2.name 
and price>avg_price

Answer 3

也不知道理解了lz的意思没，而且逻辑有点绕，lz自测一下
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 4

没懂楼主的意思？
40+20也大于50不是吗？select * from (
select id,price,sum(price) over(order by id) as sumPrice from (
select '1' as id, 40 as price from dual
union
select '1',30 from dual
union
select '1',20 from dual
union
select '1',10 from dual
union
select '2',90 from dual
union
select '2',32 from dual
union
select '2',20 from dual
union
select '2',10 from dual
)
)
where price/sumPrice > 0.2
--这个结果是每条记录与分类合计相比的结果
ID PRICE                  SUMPRICE               
-- ---------------------- ---------------------- 
1  30                     100                    
1  40                     100                    
2  90                     252