请问mysql支持同时查询前三名和倒数三名的语句吗？

[abigbigproblem]

我有类似的一个表。


想求出“前三名”和“倒数三名”的内容。能用一条sql语句查出来么？

还是只能分开来查询？

[zjcxc]

select * from(select * from(select * from tb order by score desc limit 3) a order by score  limit 1) a
union all
select * from(select * from(select * from tb order by score limit 3) a order by score desc limit 1)b

• 打赏
• 举报

回复 赞

[SCH_Enginer]

--建表语句
create table csdn1
(
id int primary key auto_increment,
SName varchar(20) not null default '',
ClsNO varchar(20) not null default '',
Score int not null default 0
)engine myisam charset utf8;

--插入语句
insert into csdn1
(SName,ClsNO,Score)
VALUES
('AAAA','C1',34),
('BBBB','C1',78),
('CCCC','C1',78),
('DDDD','C1',90),
('EEEE','C1',97),
('FFFF','C1',45),
('GGGG','C1',67),
('HHHH','C1',58),
('IIII','C1',60),
('JJJJ','C1',90),
('KKKK','C1',23),
('LLLL','C1',12),
('MMMM','C1',56),
('NNNN','C1',67),
('OOOO','C1',40),
('PPPP','C1',31),
('QQQQ','C1',55)

--sql语句
(select * from csdn1 order by Score desc limit 3 )
union
(select * from csdn1 order by Score asc limit 3)

• 打赏
• 举报

回复 赞

[abigbigproblem]

明白了，看样子我的基础还是不行阿....没有想起用union语句....

• 打赏
• 举报

回复 赞