1. 有一张表t_stu；其中三个字段:name,gender,grade；要求用一条sql语句查出男生前5名和女生前五名。
这里使用postgreSQL数据库
-- Create the tableCREATE TABLE intense.t_stu( id numeric(24),name character varying(20), gender numeric(1), grade numeric(4) );-- Truncate the tabletruncate table intense.t_stu;-- Insert datainsert into intense.t_stu values(1,'John',1,93); insert into intense.t_stu values(2,'Lisa',0,94); insert into intense.t_stu values(3,'John2',1,83); insert into intense.t_stu values(4,'Lisa2',0,84); insert into intense.t_stu values(5,'John3',1,73); insert into intense.t_stu values(6,'Lisa3',0,74); insert into intense.t_stu values(7,'John4',1,63); insert into intense.t_stu values(8,'Lisa4',0,64); insert into intense.t_stu values(9,'John5',1,53); insert into intense.t_stu values(10,'Lisa5',0,54); insert into intense.t_stu values(11,'John6',1,103); insert into intense.t_stu values(12,'Lisa6',0,104);-- Get the data neededselect * from intense.t_stu a where (select count(*) from intense.t_stu where gender=a.gender and grade > a.grade) < 5 order by a.grade desc
a表按照grade由高到低排列，并和t_stu连接，条件是gender相等并且grade大于a表的grade(返回10条记录，count=4略去)：
如果不考虑gender，只求前5名：
select * from intense.t_stu a where (select count(*) from intense.t_stu where grade > a.grade) < 5 order by a.grade desc
2. 有一张表t_game，记录了游戏玩家的id，游戏玩家名name，和每个玩家玩的游戏game，请找出玩游戏最多的那个玩家。
-- Create the table CREATE TABLE intense.t_game( id numeric(24),name character varying(20), game character varying(20));-- Truncate the tabletruncate table intense.t_game;-- Insert datainsert into intense.t_game values(1,'John','game');insert into intense.t_game values(2,'Lisa','game2');insert into intense.t_game values(3,'Jack','game3');insert into intense.t_game values(4,'Jim','game4');insert into intense.t_game values(3,'Jack','game5');insert into intense.t_game values(5,'Eric','game6');insert into intense.t_game values(1,'John','game7');insert into intense.t_game values(3,'Jack','game8');insert into intense.t_game values(6,'Nicolas','game9');-- Get the data needed-- MySQL, postgreSQLselect id,name from intense.t_game group by id, name order by count(*) desc limit 1-- SQL Serverselect TOP 1 id,name from intense.t_game group by id, name order by count(*) desc-- Oracle