求助sql语句 - 编程论坛

#2

小仙2008-11-05 22:08

网吧没有SQL，我是脑子想的。
select so.soname as '课程名' ,st.sname as '学生名字'
from stu as st join score as sc
on st.sid = sc.sid join sourses as so on
so.soid = sc.soid
group by st.sid
having min(sc.scores)>=60
你试下看行不行，楼下帮忙指正下。

#3

jxyga1112008-11-06 14:54

select a.name, b.name,scores from a left join c on a.id=c.id left join b on b.no=c.no where c.scores >= 60 group by a.id,a.name,b.name,scores

#4

ql20082008-11-06 15:33

select x.sname,ch.scores, k.soname
from stu x.score ch,sourses k
where x.sid=ch.sid and ch.soid=k.soid and ch.scores>=60
order by scorses

#5

ql20082008-11-06 15:35

from句的“.”改为“,”

#6

guo_jianwen2008-11-06 16:00

select * from stu
where sid in(
  select sid from score
  GROUP BY sid
  HAVING MIN(scores) >= 60
)

#7

wencan832008-11-06 23:22

SELECT * FROM stu WHERE sid IN
(SELECT sid FROM (SELECT sid,SUM(soid) FROM score GROUP BY sid WHERE scores>=60 ) WHERE SUM(soid)
=(SELECT SUM(soid) FROM sourses))

就这样一条句子查询语句就搞定

#8

小仙2008-11-07 17:57

select st.sname as '学生名字'
from stu as st join score as sc
on st.sid = sc.sid join sourses as so on
so.soid = sc.soid
group by st.sid,st.sname
having min(sc.scores)>=60
上面的group by写错了，应该加个st.sname

#9

commander52008-11-09 00:45

谢谢
大家了
哎
都怪自己学艺不精啊
看来以后要加倍努力了

#10

provoke2008-11-09 12:36

select ...
where stu.sid not in (
select distinct score.sid from score where scores < 60)
and stu.sid in (select distinct score.sid from score)

1、成绩表中任何不及格科目对应的学号，即为有不及格科目的学生；
2、余下的学号中，再把成绩表中没有的学号过滤掉，即过滤掉那些学生表中有但成绩表中没有（可理解为暂未记录成绩）的学生信息，剩下的就是全部都及格的了。
3、选择列表可根据实际需要使用各种连接。