求一条sQL语句(50分)

  • 主题发起人 主题发起人 cf83325
  • 开始时间 开始时间
C

cf83325

Unregistered / Unconfirmed
GUEST, unregistred user!
表student<br>name &nbsp; subject &nbsp;Result<br>张三 &nbsp; 英语 &nbsp; &nbsp; 99<br>李四 &nbsp; 英语 &nbsp; &nbsp; 70<br>王五 &nbsp; 英语 &nbsp; &nbsp; 60<br>张三 &nbsp; 数学 &nbsp; &nbsp; 70<br>李四 &nbsp; 数学 &nbsp; &nbsp; 100<br>王五 &nbsp; 数学 &nbsp; &nbsp; 60 <br>张三 &nbsp; 语文 &nbsp; &nbsp; 56<br>李四 &nbsp; 语文 &nbsp; &nbsp; 75<br>王五 &nbsp; 语文 &nbsp; &nbsp; 99<br><br>查询结果“<br>张三 &nbsp; 英语 &nbsp; &nbsp; 99<br>李四 &nbsp; 数学 &nbsp; &nbsp; 100<br>王五 &nbsp; 语文 &nbsp; &nbsp; 99<br>怎么写？？？？？？<br>小弟在SQL上是新手，很弱。
 
select subject,<br>&nbsp; &nbsp; &nbsp; &nbsp;result,<br>&nbsp; &nbsp; &nbsp; &nbsp;(select name<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from student s<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where s.subject = a.subject<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and s.result = a.result) as name<br>&nbsp; from (select subject, max(result) as result from student group by subject) a<br>或者<br>select s.subject, s.result, s.name<br>&nbsp; from (select subject, max(result) as result from student group by subject) a,<br>&nbsp; &nbsp; &nbsp; &nbsp;student s<br>&nbsp;where a.subject = s.subject<br>&nbsp; &nbsp;and a.result = s.result
 
忘记了一件事，最高分如果是二个人会有问题。第一条SQL会有问题，第二条就不会。
 
select name,subject,isnull(max(Result),0) as fs from student<br>group by name,subject
 
select name,subject,isnull(max(Result),0) as fs from student<br>group by name,subject
 
虽然是简单的SQL语句，上面没有一个人写的是正确的。。。。。。。
 
虽然简单，能不能有一个人把它写对啊
 
从给出的说明来看有两种情况,1是求学科最高分的学生,2是求学生最高分的学科,以下是SQL语句,在不同的数据库存中是不同的<br><br>求学生最高分的学科<br>select name , &nbsp;subject , max(Result) Result from student group by name,subject,<br>求学科最高分的学生(oracle)<br>select name,subject ,Result from student where subject || to_char(Result) in (select subject || to_char(max(Result)) from student group by subject ) <br>求学科最高分的学生(sql server)<br>select name,subject ,Result from student where subject + case(Result as varchar) in (select subject + case(max(Result) as varchar) from student group by subject )
 
select * from student where rownum&lt;4 order by Result desc
 
对了这是ORACLE中的写法。
 
语句很简单，但没有数据库可能结果不太清楚，最好发问题时将原数据库一起贴出来。这样结果出来的会快一些。有真实数据库一般人应该都能解决此问题。
 
cf83325的数据库一定不是ORACLE或者sql server，（下次请把问题放在 数据库-文件型）如果你是用Access的话我的语句会有字段循环引用。改一下字段别名就可以了<br>select s.name, s.subject, s.result<br>&nbsp; from (select subject, max(result) as result2 from student group by subject) a,<br>&nbsp; &nbsp; &nbsp; &nbsp;student s<br>&nbsp;where a.subject = s.subject<br>&nbsp; &nbsp;and a.result2 = s.result
 
好象这样的吧？selec * from student where result&gt;=99;
 
select distince name &nbsp;from student left join<br>(select subject ,max(Result) result from student group by subject) ttttt<br>on student.subject = ttttt.subject and student.result = ttttt.result
 
帮你写了还GGYY，随手写的不对你可以说。埋怨什么？我会记住ID的！
 
selec * from student where result&gt;=99; &nbsp;这样不就可以了！
 
我靠shadowpj你，你以为我真的是新手。昨天公司来了应聘，我就贴出来了。。。。。你记住我的ID又能怎么样。。。。我现在都不用delphi了。转JAVA克了。。。。。。。。
 
您必须登录或注册才可回复。

Similar threads

T
一条多表操作的SQL语句拦住我了，请大侠进来指点~~~~~~~ 急！！！！(100分)
回复
8
查看
279
tommy_linux
T
J
咋能一句SQL就搞定？(50分)
回复
13
查看
162
jobsxy
J
N
改写一条语句50分！(50分)
2
回复
22
查看
455
nywjx
N
I
请教 2 个 sql 语句。 (100分)
回复
16
查看
199
pengjinlongex
P
L
求教！(200分)
回复
11
查看
163
wenhong
W
后退
顶部