求教一个sql

[侯老爷]

上面的数据 根据 公式 得出下面的结果

脑壳绞起了。。写不来了。

需要用sql读出来哈，，读出数据写代码用程序运算什么的就不用说了。。。

大神快指点指点。。感激不尽。

[chengccy]

WITH TMP AS
 (SELECT 'a1' A, '2' AS B, 100 AS C
  FROM   DUAL
  UNION ALL
  SELECT 'a2' A, 'a1' AS B, 200 AS C
  FROM   DUAL
  UNION ALL
  SELECT 'a3' A, 'a1' AS B, 300 AS C
  FROM   DUAL
  UNION ALL
  SELECT 'b1' A, '5' AS B, 400 AS C
  FROM   DUAL
  UNION ALL
  SELECT 'b2' A, 'b1' AS B, 500 AS C
  FROM   DUAL)
SELECT A, B, C,
       ROUND(MAX(X) OVER(PARTITION BY P) * C / SUM(C) OVER(PARTITION BY P),
              2) D
FROM   (SELECT T.*,
                CASE
                   WHEN REGEXP_REPLACE(B, '\d') IS NULL THEN
                    A
                   ELSE
                    B
                 END AS P,
                CASE
                   WHEN REGEXP_REPLACE(B, '\d') IS NULL THEN
                    B
                 END X
         FROM   TMP T)

[侯老爷]

谢谢楼上2位的解答。。3楼的完美解决需求。。 2楼的估计也不差。。。这几个分区函数没用过。 我了解了解去。。谢谢了老铁。

[侯老爷]

csdn没人了

[卖水果的net]

select first_value（c2） over（partition by c1 order by c2） * sum（c3） over（partition by c1） from mytable