问题发生了变化，需要4个表的关联了，请帮忙看下

显示全部楼层 · 2007-7-4 17:27:50

对问题的详细解释，有部分改动，以此为准。
A
IDKSKM KSFS
-----------------------
1A80
1B75
2A90
2C70
3B66
3D55
ABCD 表示不同的科目编号
B表
（在读学生表）
ID XM NL SZQY
----------------------
1 张三20 1
3 王五19 2
SZQY字段的1，2 表示不同的所在区域
C表
ID XM NL SZQY（毕业学生表）
----------------------
2 李四21 1

A表中的ID 不在B 表就是在C表

D 表（联系方式表）
ID TEL
-----------------------------
113303333033
213304444044
313305555055

要求结果：例如要查询SZQY 为1的考生的考试科目数、考试总分和联系方式
ID XM NL KSKMSKSZF
TEL
-----------------------------------------------------------
1 张三202 155 (80+75)13303333033
2 李四212 160 (90+70)13304444044
需要4个表进行关联了
[ 本帖最后由 samfox 于 2008-1-17 15:31 编辑 ]

千问 · 2007-7-4 17:27:50

Answer:
select tmp1.ID,
tmp1.XM,
tmp1.NL,
count(*) KSKMS,
sum(tmp2.KSFS) KSZF,
tmp3.TEL
from (select ID,

XM,

NL

from tb_b
where SZQY = 1
union all
select ID,

XM,

NL
from tb_c
where SZQY = 1) tmp1,
tb_a tmp2,
tb_d tmp3
where tmp2.ID = tmp1.ID
and tmp1.ID = tmp3.ID
group by tmp1.ID, tmp1.XM, tmp1.NL, tmp3.TEL;

千问 · 2007-7-4 17:27:50

贴出来

千问 · 2007-7-4 17:27:50

刚才没有加入联系方式表时 2位大侠给出的答案都行，但加入第四个表就出现问题了，结果出不来
zhangweicai74---
这样的吧
select a.id,e.xm,e.nl,count(e.kskm),sum(a.ksfs)
from
a,
(select * from b
union all
select * from c) e
where a.id=e.id
group by a.id,e.xm,e.nl

bell6248 --
Answer:
select tmp1.ID,
tmp1.XM,
tmp1.NL,
count(*) KSKMS,
sum(tmp2.KSFS) KSZF
from (select ID,

XM,

NL

from tb_b
where SZQY = 1
union all
select ID,

XM,

NL
from tb_c
where SZQY = 1) tmp1,
tb_a tmp2
where tmp2.ID = tmp1.ID
group by tmp1.ID, tmp1.XM, tmp1.NL;

千问 · 2007-7-4 17:27:50

答案上面已经给了！

千问 · 2007-7-4 17:27:50

create table a(id int,kskm char(1),ksfs int);
insert into a values(1,'A',80);
insert into a values(1,'B',75);
insert into a values(2,'A',90);
insert into a values(2,'C',70);
insert into a values(3,'B',66);
insert into a values(3,'D',55);
create table b (id int,xm varchar2(20),nl int,szqy int);
insert into b values(1,'张三',20,1);
insert into b values(3,'王五',19,2);
create table c (id int,xm varchar2(20),nl int,szqy int);
insert into c values(2,'李四',21,1);

create table d (id int,tel varchar2(18));
insert into d values(1,'13303333033');
insert into d values(2,'13304444044');
insert into d values(3,'13305555055');
select fs.id,mem.xm,mem.nl,fs.kskms,fs.kszf,d.tel
from
(select id,count(distinct kskm) kskms,sum(ksfs) kszf
from a group by id
) fs,
(
select * from b
union
select * from c
) mem,d
where fs.id=mem.id
and fs.id=d.id

千问 · 2007-7-4 17:27:50

原帖由阿日于 2008-1-17 16:03 发表
create table a(id int,kskm char(1),ksfs int);
insert into a values(1,'A',80);
insert into a values(1,'B',75);
insert into a values(2,'A',90);
insert into a values(2,'C',70);
insert into a values(3,'B',66);
insert into a values(3,'D',55);
create table b (id int,xm varchar2(20),nl int,szqy int);
insert into b values(1,'张三',20,1);
insert into b values(3,'王五',19,2);
create table c (id int,xm varchar2(20),nl int,szqy int);
insert into c values(2,'李四',21,1);

create table d (id int,tel varchar2(18));
insert into d values(1,'13303333033');
insert into d values(2,'13304444044');
insert into d values(3,'13305555055');
select fs.id,mem.xm,mem.nl,fs.kskms,fs.kszf,d.tel
from
(select id,count(distinct kskm) kskms,sum(ksfs) kszf
from a group by id
) fs,
(
select * from b
union
select * from c
) mem,d
where fs.id=mem.id
and fs.id=d.id

感谢阿日的严谨有效的回答！以上处理方式能得出正确结果，谢谢！
同时感谢 zhangweicai74 和 bell6248给予的指导！