如何缩短这样的sql语句?

newbuding

想做个查询每班考试的平均分优秀率及格率的sql，没想到程序竟然生成了这么长的语句，请问又没有简单的方法作下面查询的？

1. 
   
2. select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级一班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级一班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级一班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级二班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级二班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='一年级二班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级一班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级一班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级一班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级三班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级三班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级三班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级二班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级二班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='三年级二班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级一班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级一班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级一班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级二班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级二班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='二年级二班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级一班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级一班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级一班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级三班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级三班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级三班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级二班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级二班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='五年级二班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级一班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级一班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级一班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级三班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级三班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级三班' union select max(class.name), count(*), avg(score), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级二班') as real)/count(*), cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60 and achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级二班') as real)/count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.schoolstu_id=schoolstu.id and achievement.exam_id=exam.id and schoolstu.class_id=class.id and schoolstu.grade_id=grade.id and schoolstu.department_id=department.id and exam.course_id=course.id and exam.name='2005-2006学年度第二学期第一次月考' and course.name='语文' and class.name='四年级二班'
   

复制代码

结果：

1. 
   
2. max        count        avg        ?column?        ?column?
   
3. 一年级一班        18        95.6388888889        0.888888888889        1.0
   
4. 一年级二班        21        87.6904761905        0.857142857143        0.904761904762
   
5. 三年级一班        22        93.6136363636        0.909090909091        1.0
   
6. 三年级三班        22        87.1818181818        0.727272727273        0.954545454545
   
7. 三年级二班        21        95.2380952381        0.952380952381        1.0
   
8. 二年级一班        23        91.9782608696        0.95652173913        0.95652173913
   
9. 二年级二班        22        95.3636363636        1.0        1.0
   
10. 五年级一班        25        87.16        0.24        1.0
    
11. 五年级三班        24        86.4791666667        0.416666666667        1.0
    
12. 五年级二班        25        82.62        0.32        0.96
    
13. 四年级一班        31        87.0161290323        0.677419354839        0.967741935484
    
14. 四年级三班        30        92.1        0.866666666667        0.966666666667
    
15. 四年级二班        31        91.9193548387        0.709677419355        1.0
    

复制代码

ssssss24

你用的啥程序生成的这个sql ?

newbuding

自己用python写的程序

bleem1998

newbuding

有关表的内容我贴在这里，好心人帮忙看看吧

http://bbs.pgsqldb.com/index.php ... 7f3b62fc7eded2cd54a

bleem1998

貌似计算优秀率and及格率？

bleem1998

select xxxxxx  from xxxxxx where xxx in (1班，2班，3班)
用这样的形式可以吗

newbuding

恐怕不行，因为优秀率每个年级是不同的，而且也没存放在数据库中，
比方说一、二年级的是95分优秀，三年级的是90分优秀，四、五年级的是85分优秀
因为优秀率的不同，我不知道这样的sql语句能不能写出来？
光是计算一个班的优秀率就够麻烦的了

bleem1998

我记得oracle的SQL里似乎是有一种语法
这个语法可以在select xxxx from yyy的xxxx中实现if条件选择
用这样的语法应该可以做到不同年级不同标准吧
具体什么语法我忘记鸟
pgsql更不知道有没有这样的语法
期待高手解答阿

Namelessxp

长点好，看起来有成就感

建议楼主先把语句整理成下面这种格式，你可以发现存在一定的规律，我对你是不是能看明白你自己贴的那一堆东西持怀疑态度

1. 
   
2. select max(class.name), count(*), avg(score),
   
3. cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=90
   
4. and achievement.schoolstu_id=schoolstu.id
   
5. and achievement.exam_id=exam.id
   
6. and schoolstu.class_id=class.id
   
7. and schoolstu.grade_id=grade.id
   
8. and schoolstu.department_id=department.id
   
9. and exam.course_id=course.id
   
10. and exam.name='2005-2006学年度第二学期第一次月考'
    
11. and course.name='语文'
    
12. and class.name='一年级一班') as real)/count(*),
    
13. cast((select count(*) from achievement,schoolstu,department,grade,class,exam,course where achievement.score>=60
    
14. and achievement.schoolstu_id=schoolstu.id
    
15. and achievement.exam_id=exam.id
    
16. and schoolstu.class_id=class.id
    
17. and schoolstu.grade_id=grade.id
    
18. and schoolstu.department_id=department.id
    
19. and exam.course_id=course.id
    
20. and exam.name='2005-2006学年度第二学期第一次月考'
    
21. and course.name='语文'
    
22. and class.name='一年级一班') as real)/count(*)
    

复制代码

[ 本帖最后由 Namelessxp 于 2006-5-17 10:26 编辑 ]