select sid,name,max(score) from tablename group by sid,name 才可以
nononono is right .i've tested it;
如果是: sid lid score --------------------- 201 shuxue 65 201 lishi 80 ▲ 203 wuli 75 ▲ 203 huaxue 68 203 shuxue 72 205 shengwu 88 205 yuwen 90 ▲ 选出每个sid中score最高的记录(sid,lid,score) 如果按nononono的方法(select sid,lid,max(score) from tablename group by sid)会出来 Column 'tablename.sid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'tablename.lid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
哦。我写的是不通用。在VFP中可以,在MS SQL Server 7中会出现: Column 'tablename.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.wuccc的才完全。
wuccc的方法也有问题,会把记录全部选出来!!!!
"全部选出来"? 不会吧。 只有一种情况会全部选出来:sid与name的值不是一一对应的。
sid与name的值不是一一对应的时候也不会全部选出来,只会出不同的部分
SELECT Max(score) AS max, sid AS newSid, name AS newName INTO qqq FROM tablename GROUP BY sid 注:qqq 为新表
select sid,score=max(score) from tableName group by sid
第一种情况用 select sid,name,max(score) from tablename group by sid,name 第二种可以用 create table #temp_score ( myid int null, myname char(20) null, myscore int )create unique index #temp_score_11 on #temp_score(myid) with ignore_dup_keyinsert into #temp_score select a.myid,a.myname,a.myscore from score as a join (select (b.myid)as myid,max(b.myscore) as score from score as b group by b.myid) as b on (a.myid=b.myid and a.myscore=b.score) 你最好改一下数据结构! 加个id字段啊
Select a.Sid,a.Name,a.Score From tablename a Where a.Score=(Select Max(b.Score) From tablename b Where a.Sid=b.Sid)--Over! --That's OK!
select t1.* from tablename t1 join (select Sid,max(Score ) Score from tablename Group By Sid) t2 on t1.Sid=t2.Sid and t1.Score =t2.Score 如果!一个学生的成绩都是80分!你就要显示他的两个最高分!不是一个例如 201 shuxue 80 201 lishi 80 情况
我的意思是,显示每个学生(SID)的成绩最高的科目(SID,LID,SCORE),不用临时表,因为我担心临时表影响性能(创建删除)。(?) 下面语句可以实现: Select a.Sid,b.Lid,a.Score from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b where b.Sid=a.Sid 不过我只是举了个简单的例子,实际上我的数据库要联结的表有8个以上,所以显得比较慢(如果数据比较多的话。不过我不知道怎么算复杂度)
你的写法不对啊朋友!如果输入是201 shuxue 80 201 lishi 80 ▲ 203 wuli 75 ▲ 203 huaxue 68 203 shuxue 72 205 shengwu 88 205 yuwen 90 用Select a.Sid,b.Lid,a.Score from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b where b.Sid=a.Sid 结果是 201 shuxue 80 201 lishi 80 203 wuli 75 203 huaxue 68 203 shuxue 72 205 shengwu 88 205 yuwen 90
你的写法不对啊朋友!如果输入是201 shuxue 80 201 lishi 80 ▲ 203 wuli 75 ▲ 203 huaxue 68 203 shuxue 72 205 shengwu 88 205 yuwen 90 用Select a.Sid,b.Lid,a.Score from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b where b.Sid=a.Sid 结果是 201 shuxue 80 201 lishi 80 203 wuli 75 203 huaxue 75 203 shuxue 75 205 shengwu 90 205 yuwen 90
不好意思少写了一个条件,应该是: Select a.Sid,b.Lid,a.Score from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b where b.Sid=a.Sid and b.score=a.score
在oracle中的答案, SELECT sid,MAX(lid),MAX(score) FROM tablename OK WHERE score=(SELECT MAX(c) FROM tablename KO WHERE KO.sid=OK.sid) GROUP BY sid; 好像不太像吧,但这是一个正确答案.
sid lid score
---------------------
201 shuxue 65
201 lishi 80 ▲
203 wuli 75 ▲
203 huaxue 68
203 shuxue 72
205 shengwu 88
205 yuwen 90 ▲
选出每个sid中score最高的记录(sid,lid,score)
如果按nononono的方法(select sid,lid,max(score) from tablename group by sid)会出来
Column 'tablename.sid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'tablename.lid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'tablename.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.wuccc的才完全。
只有一种情况会全部选出来:sid与name的值不是一一对应的。
注:qqq 为新表
select sid,name,max(score) from tablename group by sid,name
第二种可以用
create table #temp_score
(
myid int null,
myname char(20) null,
myscore int
)create unique index #temp_score_11 on #temp_score(myid) with ignore_dup_keyinsert into #temp_score
select a.myid,a.myname,a.myscore
from score as a join
(select (b.myid)as myid,max(b.myscore) as score from score as b group by b.myid) as b
on (a.myid=b.myid and a.myscore=b.score)
你最好改一下数据结构!
加个id字段啊
Where a.Score=(Select Max(b.Score) From tablename b
Where a.Sid=b.Sid)--Over!
--That's OK!
from tablename t1 join
(select Sid,max(Score ) Score from tablename Group By Sid) t2
on t1.Sid=t2.Sid and t1.Score =t2.Score
如果!一个学生的成绩都是80分!你就要显示他的两个最高分!不是一个例如
201 shuxue 80
201 lishi 80
情况
下面语句可以实现:
Select a.Sid,b.Lid,a.Score
from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b
where b.Sid=a.Sid
不过我只是举了个简单的例子,实际上我的数据库要联结的表有8个以上,所以显得比较慢(如果数据比较多的话。不过我不知道怎么算复杂度)
201 lishi 80 ▲
203 wuli 75 ▲
203 huaxue 68
203 shuxue 72
205 shengwu 88
205 yuwen 90
用Select a.Sid,b.Lid,a.Score
from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b
where b.Sid=a.Sid
结果是
201 shuxue 80
201 lishi 80
203 wuli 75
203 huaxue 68
203 shuxue 72
205 shengwu 88
205 yuwen 90
201 lishi 80 ▲
203 wuli 75 ▲
203 huaxue 68
203 shuxue 72
205 shengwu 88
205 yuwen 90
用Select a.Sid,b.Lid,a.Score
from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b
where b.Sid=a.Sid
结果是
201 shuxue 80
201 lishi 80
203 wuli 75
203 huaxue 75
203 shuxue 75
205 shengwu 90
205 yuwen 90
Select a.Sid,b.Lid,a.Score
from (select Sid,Score=Max(Score) from tablename group by Sid) a,tablename b
where b.Sid=a.Sid and b.score=a.score
id lid score
----------- -------------------- -----------
201 shuxue 80
201 lishi 80
203 wuli 75
203 huaxue 68
203 shuxue 68
205 shengwu 88
205 yuwen 90
其结果是
id lid Score
----------- -------------------- -----------
201 shuxue 80
201 lishi 80
203 wuli 75
205 yuwen 90
这样出现两个最高分!同一个人显示两次!
我想这个问题不对!以为sql 语句是先连接后提取数据!
其实你的方法和我的一样!我的临时表仅仅是用来过滤出现同一个人有两门一样最高的分数的问题
SELECT sid,MAX(lid),MAX(score)
FROM tablename OK
WHERE score=(SELECT MAX(c)
FROM tablename KO
WHERE KO.sid=OK.sid)
GROUP BY sid;
好像不太像吧,但这是一个正确答案.
id lid score
----------- -------------------- -----------
201 shuxue 80
201 lishi 80
203 wuli 75
203 huaxue 68
203 shuxue 68
205 shengwu 88
205 yuwen 90
其结果是
id lid Score
----------- -------------------- -----------
201 shuxue 80
201 lishi 80
203 wuli 75
205 yuwen 90
这样出现两个最高分!同一个人显示两次!
我想这个问题不对!以为sql 语句是先连接后提取数据!
其实你的方法和我的一样!我的临时表仅仅是用来过滤出现同一个人有两门一样最高的分数的问题
大家为什么要把我说的这一种情况不要你