(select Max(score) from TABLE_NAME where subject=0)
union
(select Max(score) from TABLE_NAME where subject=1)
union
(select Max(score) from TABLE_NAME where subject=2)
union
(select Max(score) from TABLE_NAME where subject=1)
union
(select Max(score) from TABLE_NAME where subject=2)
否则的话使用max就可以了
对了,使用集函数或者用union
那应该是这样:
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=0)
)
union
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=1)
)union
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=2)
)
from
(
select max(score) as conscore, subject
from tablename
group by suject
) b join tablename a
on
a.subject=b.subject
and
b.couscore=a.score
group by id,a.subject我使用的是 sql server 2000,
我在我的数据库上已经试过,完全可以!
(
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=0)
) and subject=0
)
union
(
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=1)
) and subject=1
)
union
(
select id from TABLE_NAME where score in(
(select Max(score) from TABLE_NAME where subject=2)
) and subject=2
)===================================================
倒是,不知道SUBJECT有几种应该是个问题。
where TableName.score= 科目最高分表.最高分
select id from (select max(score) as 最高分 from tablename group by subject) as 科目最高分表, tablename
where tablename.score= 科目最高分表.最高分
landro你的方法我现在无法验证。明天试一下。
------------------------------对,有这个缺陷那就这样select id from (select max(score) as 最高分,subject from tablename group by subject) as 科目最高分表, tablename
where tablename.score= 科目最高分表.最高分 and tablename.subject=科目最高分表.subject
我用sqlsever测试了的
from aa as a,(select subject,max(score) as score
from aa
group by subject) as b
where a.subject=b.subject and a.score=b.score
order by a.subject ,a.id
1.
select id, b.max_score, b.subject from (select max(score) as max_score, subject from scores group by subject ) b join
( select id, score, subject from scores ) a
on b.max_score = a.score and b.subject= a.subject 2.
select id, max_score, a.subject from (select max(score) as max_score, subject from scores group by subject) b, scores a
where a.subject= b.subject and a.score= b.max_score