求SQL语句....

Question

现有TABLE1:ID      F1     F2    F3
001     A      4     7
002     A      3     5 
003     B      4     3
004     B      1     2
005     B      5     4
006     C      2     3欲得到如下结果:
(即A=7/4+5/3=41/12   B=3/4+2/1+4/5=71/20 如此类推)TABLE2:F1   F2    F3
A    12    41 
B    20    71
C    2     3

Answer 1

--是求和吗？
select F1,sum(F2),sum(F3)
from table1
group by F1

Answer 2

回楼上的,非也,实为分数的相加select F1,sum(F2),sum(F3)
from table1
group by F1的结果为: 
F1   F2   F3
A    11   15
B    5    5
C    2    3

Answer 3

老zhuo！明显不是求和吧？
楼主把需求再说下吧！没看懂

Answer 4

呵呵 看到12了 刚好F3是5 和7 
zhuo=卓

Answer 5

最简没思路,一般的这样应该就可以了
SQL> select * from table1;
 
ID                                                                               F1                                                                                       F2         F3
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------
001                                                                              A                                                                                         4          7
002                                                                              A                                                                                         3          5
003                                                                              B                                                                                         4          3
004                                                                              B                                                                                         1          2
005                                                                              B                                                                                         5          4
006                                                                              C                                                                                         2          3
 
6 rows selected
 
SQL> create or replace function func_get_num(i_str varchar2) return number
  2  as
  3  v_num number;
  4  begin
  5   execute immediate 'select '||i_str||' from dual' into v_num;
  6   return v_num;
  7  end;
  8  /
 
Function created
 
SQL> select f1,
  2         func_get_num(replace(f2, ',', '*')) f2,
  3         sum(func_get_num(replace(f2, ',', '*')) * f3 / f2_old) f3
  4    from (select f1, wm_concat(f2) over(partition by f1) f2, f3, f2 f2_old
  5            from table1)
  6   group by f1, func_get_num(replace(f2, ',', '*'));
 
F1                                                                                       F2         F3
-------------------------------------------------------------------------------- ---------- ----------
A                                                                                        12         41
C                                                                                         2          3
B                                                                                        20         71
 
SQL> 

Answer 6

SELECT N.F1,M.F2,SUM(N.F3*M.F2/N.F2)
from
(select  F1, power(10,sum(log(10,F2)))AS F2   from   TABLE1 GROUP BY F1)M,TABLE1 N
WHERE M.F1=N.F1
GROUP BY N.F1,M.F2试一下，非最简

Answer 7

楼上数学学的好啊
SQL> SELECT N.F1,M.F2,SUM(N.F3*M.F2/N.F2)
  2  from
  3  (select F1, power(10,sum(log(10,F2)))AS F2 from TABLE1 GROUP BY F1)M,TABLE1 N
  4  WHERE M.F1=N.F1
  5  GROUP BY N.F1,M.F2
  6  ;
 
F1                                                                                       F2 SUM(N.F3*M.F2/N.F2)
-------------------------------------------------------------------------------- ---------- -------------------
A                                                                                        12                  41
B                                                                                        20                  71
C                                                                                         2                   3
 
SQL> 

Answer 8

这样就不需要自己处理连乘的问题,相当于是变连乘为指数和加法运算.绕开了oracle没连乘函数的缺陷.

Answer 9

 power(10,sum(log(10,F2))) 嗯  其妙的累乘......不过这样子是否会有误差 再研究研究....唉 数学太差中学的东西我都忘光了

Answer 10

create or replace function func_common_divisor(i_num1 integer,
                                               i_num2 integer)
  return integer as
  v_num         integer;
  v_max_divisor integer;
begin
  select decode(sign(abs(i_num1) - abs(i_num2)),
                -1,
                abs(i_num1),
                abs(i_num2))
    into v_num
    from dual;
  for i in 1 .. v_num loop
    if mod(i_num1, i) = 0 and mod(i_num2, i) = 0 then
      v_max_divisor := i;
    end if;
  end loop;
  return v_num;
end;

Answer 11

函数返回值写错了
create or replace function func_common_divisor(i_num1 integer,
                                               i_num2 integer)
  return integer as
  v_num         integer;
  v_max_divisor integer;
begin
  select decode(sign(abs(i_num1) - abs(i_num2)),
                -1,
                abs(i_num1),
                abs(i_num2))
    into v_num
    from dual;
  for i in 1 .. v_num loop
    if mod(i_num1, i) = 0 and mod(i_num2, i) = 0 then
      v_max_divisor := i;
    end if;
  end loop;
  return v_max_divisor;
end;

Answer 12

SQL> select f1,f2/func_common_divisor(cast(f2 as integer),cast(f3 as integer)),f3/func_common_divisor(cast(f2 as integer),cast(f3 as integer)) from
  2  (SELECT N.F1,M.F2,SUM(N.F3*M.F2/N.F2) f3
  3  from
  4  (select F1, power(10,sum(log(10,F2)))AS F2 from TABLE1 GROUP BY F1)M,TABLE1 N
  5  WHERE M.F1=N.F1
  6  GROUP BY N.F1,M.F2);
 
F1                                                                               F2/FUNC_COMMON_DIVISOR(CAST(F2 F3/FUNC_COMMON_DIVISOR(CAST(F2
-------------------------------------------------------------------------------- ------------------------------ ------------------------------
A                                                                                                            12                             41
B                                                                                                            20                             71
C                                                                                                             2                              3
 
SQL> 

Answer 13

使用power和log做转换的话存在的问题就是产生的数据存在精度问题.
得到的数据会是原来的整数附加一个很小的小数.需要cast成整数再进行处理.

Answer 14

SELECT N.F1,M.F2,SUM(N.F3*M.F2/N.F2)
from
(select F1, power(10,sum(log(10,F2)))AS F2 from TABLE1 GROUP BY F1)M,TABLE1 N
WHERE M.F1=N.F1
GROUP BY N.F1,M.F2--------------------
--顶下～