oracle有两个表:
【Table1】
id xh(学号) xm(姓名) xb(性别) nl(年龄)
1 001 张三 男 17
2 003 李四 女 18
3 007 王五 男 19
------------------------------------
【Table2】
id xh(学号) km(科目) fs(分数)
1 001 A 90
2 001 B 80
3 001 C 70
4 001 D 60
5 003 A 95
6 003 B 85
7 003 C 75
8 003 D 65
9 005 A 85
10 005 C 75
11 006 B 65
------------------------------------
最终想得到以下结果:
【Table_New】
id xh(学号) xm(姓名) xb(性别) nl(年龄) fsAB(分数) fsCD(分数)
1 001 张三 男 17 170 130
2 003 李四 女 18 180 140
3 007 王五 男 19 0 0
------------------------------------
1、Table1.xh=Table2.xh
2、【Table_New】显示的数据总数与【Table1】数据总数相同(左联接)
3、【字段fsAB】是【科目A】与【科目B】的和
4、【字段fsCD】是【科目C】与【科目D】的和
5、对于Table2没找到对应份数的,均以0替代。
------------------------------------
【Table1】
id xh(学号) xm(姓名) xb(性别) nl(年龄)
1 001 张三 男 17
2 003 李四 女 18
3 007 王五 男 19
------------------------------------
【Table2】
id xh(学号) km(科目) fs(分数)
1 001 A 90
2 001 B 80
3 001 C 70
4 001 D 60
5 003 A 95
6 003 B 85
7 003 C 75
8 003 D 65
9 005 A 85
10 005 C 75
11 006 B 65
------------------------------------
最终想得到以下结果:
【Table_New】
id xh(学号) xm(姓名) xb(性别) nl(年龄) fsAB(分数) fsCD(分数)
1 001 张三 男 17 170 130
2 003 李四 女 18 180 140
3 007 王五 男 19 0 0
------------------------------------
1、Table1.xh=Table2.xh
2、【Table_New】显示的数据总数与【Table1】数据总数相同(左联接)
3、【字段fsAB】是【科目A】与【科目B】的和
4、【字段fsCD】是【科目C】与【科目D】的和
5、对于Table2没找到对应份数的,均以0替代。
------------------------------------
SELECT t1.*,
NVL(t2.fsAB,0) fsAB,
NVL(t2.fsCD,0) fsCD
FROM table1 t1
LEFT JOIN (
SELECT m.xh,
MAX(DECODE(m.km, 'A', m.fs)) fsAB,
MAX(DECODE(m.km, 'C', m.fs)) fsCD
FROM (SELECT t.xh,
DECODE(t.km, 'B', 'A', 'D', 'C', t.km) km,
SUM(t.fs) fs
FROM table2 t
GROUP BY t.xh,
DECODE(t.km, 'B', 'A', 'D', 'C', t.km)) m
GROUP BY m.xh) t2 ON t1.xh = t2.xh
ORDER BY t1.id
(
SELECT '1' AS id, '001' AS xh,'張三' AS xm,'男' AS xb,'17' as nl FROM dual
union all
SELECT '2' AS id, '003' AS xh,'李四' AS xm,'女' AS xb,'18' as nl FROM dual
union all
SELECT '3' AS id, '007' AS xh,'王五' AS xm,'男' AS xb,'19' as nl FROM dual
),
table2 AS
(
SELECT '1' AS id, '001' AS xh,'A' AS km,'90' AS fs FROM dual
union all
SELECT '2' AS id, '001' AS xh,'B' AS km,'80' AS fs FROM dual
union all
SELECT '3' AS id, '001' AS xh,'C' AS km,'70' AS fs FROM dual
union all
SELECT '4' AS id, '001' AS xh,'D' AS km,'60' AS fs FROM dual
union all
SELECT '5' AS id, '003' AS xh,'A' AS km,'95' AS fs FROM dual
union all
SELECT '6' AS id, '003' AS xh,'B' AS km,'85' AS fs FROM dual
union all
SELECT '7' AS id, '003' AS xh,'C' AS km,'75' AS fs FROM dual
union all
SELECT '8' AS id, '003' AS xh,'D' AS km,'65' AS fs FROM dual
union all
SELECT '9' AS id, '005' AS xh,'A' AS km,'85' AS fs FROM dual
union all
SELECT '10' AS id, '005' AS xh,'C' AS km,'75' AS fs FROM dual
union all
SELECT '11' AS id, '006' AS xh,'B' AS km,'65' AS fs FROM dual
)
SELECT ID,
XH,
XM,
XB,
NL,
MAX(DECODE(KM, 'A', FS, 0)) + MAX(DECODE(KM, 'B', FS, 0)) AS fsAB,
MAX(DECODE(KM, 'C', FS, 0)) + MAX(DECODE(KM, 'D', FS, 0)) AS fsCD
FROM (SELECT T1.ID, T1.XH, T1.XM, T1.XB, T1.NL, T2.KM, T2.FS
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.XH = T2.XH)
GROUP BY ID, XH, XM, XB, NL
ORDER BY ID
sum(decode(t2.km,'A',t2.fs,'B',t2.fs,0)) fsAB 分数,
sum(decode(t2.km,'C',t2.fs,'D',t2.fs,0))fsCD 分数
from table1 t1,table2 t2
where t1.xh=t2.xh(+)
(select t.id,t.xh,t.xm,t.xb,t.nl,tab.fsAB,tcd.fsCD
from table1 t ,
(select xh,sum(fs) as fsAB from table2 where km in ('A','B') group by xh ) as tab,
(select xh,sum(fs) as fsCD from table2 where km in ('C','D') group by xh ) as tcd
where t.xh(+)=tab.xh and t.xh(+)=tcd.xh)
select table1.id,table1.xh,table1.xm,table1.xb,table1.nl,
sum(decode(table2.km,'A',fs,'B',fs,0)) fsAB,
sum(decode(table2.km,'C',fs,'D',fs,0))fsCD
from table1,table2
where table1.xh=table2.xh(+)
group by table1.id,table1.xh,table1.xm,table1.xb,table1.nl
order by table1.xh2楼的很好啊
sum(case when t2.km in ('A','B') then fs else 0 end) fsab,
sum(case when t2.km in ('C','D') then fs else 0 end) fscd
from table1 t1
left join table2 t2
on t2.xh=t1.xh
group by t1.xh,t1.xm,t1.xb,t1.nl;
SELECT t.id ,t.xh,t.xm ,t.xb,t.nl,
SUM(CASE WHEN d.km IN ('A','B') THEN d.fs
ELSE 0 END ) fsAB,
SUM(CASE WHEN d.km IN ('C','D') THEN d.fs
ELSE 0 END ) fsCD
FROM student t,grade d
WHERE t.xh=d.xh(+)
GROUP BY t.id ,t.xh,t.xm, t.xb,t.nl
ORDER BY t.id