--6找出各系年龄最大的学生，显示其学号、姓名；
SELECT sno,sname
FROM S
WHERE sage=
(SELECT MAX(sage)
FROM S
WHERE sdept='CS'
)
UNION
SELECT sno,sname
FROM S
WHERE sage=
(SELECT MAX(sage)
FROM S
WHERE sdept='MA'
)
UNION
SELECT sno,sname
FROM S
WHERE sage=
(SELECT MAX(sage)
FROM S
WHERE sdept='IS'
);
用这种方法查询正确但是较烦琐，以下是我用另一种方法的查询但是结果有错误，大家帮忙改下，谢谢了
/*SELECT sno,sname
FROM S
WHERE sage=
(SELECT MAX(sage)
FROM S
WHERE sdept='CS' OR sdept='IS' OR sdept='MA' );*/结果有错

[此贴子已经被作者于2007-4-22 22:51:29编辑过]

你的第二个答案是找出cs,is,ma这三个系中年龄最大的,而开始那个是找出每个系中年龄最大的,第二个找出的记录数比第一个少,要是你的表总总就三个系的话,直接这样
select sno,sname from s group by sdept having sage=max(sage)

select a.Sno, a.Sname from
(select * from s where sdept in ('MA','CS','IS')) a
join (select sno,Sdept from s group by sdept having sage=max(sage)) b
on a.Sno= b.sno and a.sdept =b.sdept

SELECT Sno, Sname
FROM S AS A
WHERE Sage =
 (SELECT MAX(Sage)
 FROM S AS B
 WHERE A.Sdept = B.Sdept);