考大家一道SQL面试题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>JobHunting - 待字闺中

考大家一道SQL面试题

考大家一道SQL面试题# JobHunting - 待字闺中

b*e2010-09-02 07:09

1 楼

table(player_name, score, time_stamp)
写一个query列出每个player的highest score跟这个record的time_stamp。

d*e2010-09-02 07:09

2 楼

这是oracle的，至于用row_number, rank 还是dense_rank就看你自己的要求了。
select *
from (select player_name, score, time_stamp
row_number() over (parition by player_name
order by score desc) r
from the_table)
where r = 1;

【在 b*****e 的大作中提到】

: table(player_name, score, time_stamp)
: 写一个query列出每个player的highest score跟这个record的time_stamp。

B*g2010-09-02 07:09

3 楼

also work for sql server 2005 or above

【在 d**e 的大作中提到】

: 这是oracle的，至于用row_number, rank 还是dense_rank就看你自己的要求了。
: select *
: from (select player_name, score, time_stamp
: row_number() over (parition by player_name
: order by score desc) r
: from the_table)
: where r = 1;

e*32010-09-02 07:09

4 楼

通用型Query:
select * from table join (select player_name, max(score) as maxscore from
table group by player_name) as t on (table.player_name = t.player_name and
table.score = t.maxscore)

n*52010-09-02 07:09

5 楼

一个group by 就搞定了？
通用query
select player_name, max(score) as maxscore, timestamp from table group by
player_name

d*e2010-09-02 07:09

6 楼

这个不能取 timestamp, 它不在group by 里面

【在 n********5 的大作中提到】

: 一个group by 就搞定了？
: 通用query
: select player_name, max(score) as maxscore, timestamp from table group by
: player_name

b*n2010-09-02 07:09

7 楼

通用query, 不知道对不对?
select table.player_name, table.score, timestamp
from table, (select player_name, MAX(score) AS maxscore) AS tmp
where table.player_name = tmp.playername AND table.score = tmp.maxscore;