请高手指教一个SQL语句

magicangel

最初由 magicangel 发布
[B]这样也可以：
select id,class,max(score),max(exam_date)
from exam_score
group by id,class
order by id

guanglin4的做法对于重复纪录要加distinct [/B]

本来我是想在我的基础上改成
select distinct a.* from exam_score a, (select id,class,max(exam_date) exam_date from exam_score group by id, class) b
where a.id=b.id and a.class=b.class and a.exam_date=b.exam_date
的，不过最近受了你的启发，我就用起exists来了。

magicangel

magicangel

test

guanglin4

很有收获。

Here are some thoughts:

1) use of distinct
for this particular case, distinct may not be necessary.  However, if we generalize the case, then the issue becomes how to get a row or rows which is max or min in certain fields. In other words, dups are allowed in the table. In that case, the use of distinct will be determined by business requirements.

2) exists or not
The test table is very small, and has no indices.  In fact, even if indices were created, they probably would not be used any way, because the cost of a table scan will be much less in that case (to the optimizer).  For bigger tables (say, over 1 million of records), things will be different.  Not exists is not ideal in that case (definitely inferior to just "exists". Max/Min will probably not fare much better either.  We will only know it through tests and query plan analysis.