有两个表,A表是主表(no是主键),B表是子表,一对多的关系A表:
no num
--------
A 10
B 20
C 30B表:
no kind
---------
A 01
A 02B 01
B 02
B 03C 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
no num
--------
A 10
B 20
C 30B表:
no kind
---------
A 01
A 02B 01
B 02
B 03C 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
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
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
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;
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
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