看看有几个人能把这个sql语句一次写成功?(300分)

数据表:student_id,name,age,class_id,group_id
[学生id][姓名][年龄][班级id][组id]
用一条sql语句
1.求低于组平均年龄的人数比率在50%以下的那些组所在班级中高于班级平均年龄的学生

2.求组内最大年龄和最小年龄的学生既不是班级最大又不是班级最小的那些组所在班级的平均年龄

[][][][]
今天在网上无意之间浏览到的
拿来以飨DFW上的程序员!

1 SELECT student_id,name,age,class_id,group_id
FROM TABLE1 AS T1,
(
SELECT class_id AS c_id1,group_id AS g_id1
FROM Table1
GROUP BY class_id,group_id
HAVING AVG(age)/SUM(age)<0.5
) T2,
(
SELECT class_id AS c_id2,AVG(a2) AS a2
FROM Table1
GROUP BY class_id
) T3
WHERE class_id=c_id1 AND group_id=g_id1 AND class_id=c_id2 AND age>a2

2 SELECT class_id,AVG(age)
FROM TABLE1 AS T1,
(
SELECT clas_id AS c_id1,group_id AS g_id1,MAX(age) AS max1,MIN(age) AS min1
FROM TABLE1
GROUP BY class_id,group_id
) T2,
(
SELECT clas_id AS c_id2,MAX(age) AS max2,MIN(age) AS min2
FROM TABLE1
GROUP BY class_id
) T3
WHERE class_id=c_id1 AND group_id=g_id1 AND c_id1=c_id2 AND max1<>max2 AND min1<>min2
GROUP BY class_id

差不多就这样

多人接受答案了。