求一sql语句

[tjm1]

有两个表，A表是主表(no是主键)，B表是子表，一对多的关系

A表:
no num
--------
A 10
B 20
C 30

B表:
no kind
---------
A 01
A 02

B 01
B 02
B 03

C 05
C 09

求一sql语句，根据B表的kind字段分组，汇总A表的num值(注意：B表中的kind字段均摊A表的num)，效果如下：
kind num
----------
01 11.67
02 11.67
03 6.67
05 15
09 15

[fxianxian]

SQL> with a as (select 'A' NO,10 NUM FROM DUAL
  2             UNION
  3             select 'B' NO,20 NUM FROM DUAL
  4             UNION
  5             select 'C' NO,30 NUM FROM DUAL
  6             ),
  7       B AS (SELECT 'A' NO,'01' KIND FROM DUAL
  8             UNION
  9             SELECT 'A' NO,'02' KIND FROM DUAL
 10             UNION
 11             SELECT 'B' NO,'01' KIND FROM DUAL
 12             UNION
 13             SELECT 'B' NO,'02' KIND FROM DUAL
 14             UNION
 15             SELECT 'B' NO,'03' KIND FROM DUAL
 16             UNION
 17             SELECT 'C' NO,'05' KIND FROM DUAL
 18             UNION
 19             SELECT 'C' NO,'09' KIND FROM DUAL
 20             )
 21  SELECT B.KIND,SUM(ROUND(A.NUM/NO1,2)) NUM FROM A,(SELECT A.NO,COUNT(B.NO) NO1 FROM A,B WHERE A.NO=B.NO
 22  GROUP BY A.NO) C,B
 23  WHERE A.NO=C.NO AND A.NO=B.NO
 24  GROUP BY B.KIND
 25  ORDER BY B.KIND
SQL> /
 
KIND        NUM
---- ----------
01        11.67
02        11.67
03         6.67
05           15
09           15

[DragonBill]

SELECT KIND, SUM(AVG_NUM) AS NUM
FROM (
      SELECT  
              ROUND(A.NUM/COUNT(B.ROWID) OVER (PARTITION BY B.NO ORDER BY B.NO),2) AVG_NUM,
              B.KIND
      FROM A, B WHERE A.NO = B.NO
     )
GROUP BY KIND

[worldly_wind]

SELECT DISTINCT B.KIND,
                SUM(ROUND(A.NUM / (SELECT COUNT(B.NO)
                                     FROM B
                                    WHERE A.NO = B.NO
                                    GROUP BY B.NO),
                          2)) OVER(PARTITION BY B.KIND)
  FROM A, B
 WHERE A.NO = B.NO;

[Croatia]

赫赫,这个问题,不用等奇迹发生的.

[ymcscu]

SELECT b_1.kind, SUM(temp.num_avg)
FROM (SELECT b.no, AVG(a.number) / COUNT(b.no) AS num_avg
FROM b INNER JOIN
a ON b.no = a.no
GROUP BY b.no, a.no) AS temp INNER JOIN
b AS b_1 ON temp.no = b_1.no
GROUP BY b_1.kind

[ymcscu]

楼上几位的关键字OVER是什么意思？？

[hebo2005]

有OVER 的都是分析函数，你GOOGLE下 ORACLE加分析函数就知道了

[ymcscu]

啊，我忘了这个是oracle版块，我5楼发的是用Sql Server做的，还要修改几个关键字

[sulins]

SELECT B.KIND, SUM(T.AVG_NUM) 
FROM B, (SELECT A.NO, A.NUM/(SELECT COUNT(*) FROM B WHERE B.NO=A.NO) AVG_NUM FROM A) T
WHERE B.NO = T.NO
GROUP BY B.KIND