▲请问这个SQL语句该怎么写，谢谢各位了！（有答案立刻结分）
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替代。
------------------------------------
[解决办法]
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
[解决办法]
WITH table1 AS
(
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
[解决办法]
select t1.id,t1.xh 学号,t1.xm 姓名,t1.xb 性别,t1.nl 年龄,
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(+)
[解决办法]
2楼的答案很正确
[解决办法]
select t1.*,sum(case when (t2.km='A'
[解决办法]
t2.km='B') && (t2.xh=001
[解决办法]
t2.xh=003
[解决办法]
t2.xh=007) then fs else 0 end) as AB分数和, sum(case when (t2.km='C'
[解决办法]
t2.km='D') && (t2.xh=001
[解决办法]
t2.xh=003
[解决办法]
t2.xh=007) then fs else 0 end) as CD分数和 from table1 as t1 left join table2 as t2 on t1.xh=t2.xh group by t1.id order by t1.id
[解决办法]
如果你是查table1表里面有的学生的话就可以不要 && (t2.xh=001
[解决办法]
t2.xh=003
[解决办法]
t2.xh=007) && (t2.xh=001
[解决办法]
t2.xh=003
------解决方案--------------------
t2.xh=007) 这2句，如果你要是只查里面某个人或几个人的成绩的话，就可以用这句话
[解决办法]
select id,xh,xm,xb,nl,nvl(fsAB,0) as fsAB,nvl(fsCD,0) as fsCD from
(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)
[解决办法]
[解决办法]
写写更健康！
[解决办法]