最近总结的mysql分组排序查询语句

2019独角兽企业重金招聘Python工程师标准>>>

(1):一个表中的分组排序

先准备数据：
1.创建一个表
create table jjyy
(
id int(11) not null auto_increment,
username varchar(50) default null,
userType varchar(30) default null,
value varchar(20),
primary key(id)
)

2.插入数据
insert into jjyy(username,userType,value) values('user1','type1','1.5');
insert into jjyy(username,userType,value) values('user1','type2','2.5');
insert into jjyy(username,userType,value) values('user1','type3','3.5');
insert into jjyy(username,userType,value) values('user2','type1','4.5');
insert into jjyy(username,userType,value) values('user2','type2','5.5');
insert into jjyy(username,userType,value) values('user2','type3','6.5');
insert into jjyy(username,userType,value) values('user3','type1','7.5');
insert into jjyy(username,userType,value) values('user3','type2','8.5');
insert into jjyy(username,userType,value) values('user3','type3','9.5');

3.问题一:

想得到每个分组的前两个value值(降序):
sql code:
   select t1.id,t1.username,t1.userType,t1.value from jjyy t1
where (select count(0) from jjyy t2 where t1.username=t2.username and t1.value<=t2.value) <=2
group by t1.username,t1.userType,t1.value;

4.问题二:

想得到每个分组的最大值（最小值同理）：
SELECT temp.id,temp.username,temp.userType,temp.value FROM (SELECT * FROM jjyy ORDER BY value desc) temp GROUP BY username;

(2):
两个相关联的表之间的分组排序(这是在网上找的一个例子自己实现了一下)
要求：每次取5条新闻,每个新闻取2个评论id [ 评论依据 hit desc排序] ，一条sql写出
格式如下：
新闻A    评论1_评论2
新闻B    评论1_评论2
  .           .
  .           .
  .           .

1.创建表
create table news
(
id int(11) not null auto_increment,
title varchar(50),
primary key(id)
)default charset=utf8

create table commends
(
id int(11) not null auto_increment,
news_id int(11),
hit int(11),
primary key(id) 

)default charset=utf8

2.导入数据
insert into news(title) values('news_A'),('news_B'),('news_C'),('news_D'),('news_F'),('news_G');
insert into commends(news_id,hit) values(1,1),(1,2),(1,3);
insert into commends(news_id,hit) values(2,1),(2,2),(2,3);
insert into commends(news_id,hit) values(3,1),(3,2),(3,3);
insert into commends(news_id,hit) values(4,1),(4,2),(4,3);
insert into commends(news_id,hit) values(5,1),(5,2),(5,3);
insert into commends(news_id,hit) values(6,1),(6,2),(6,3);

sql code:
select
    n.id,n.title,concat( ifnull(cast(max(case when m.cnt='1' then m.id end) as char),''),'-', ifnull(cast(max(case when m.cnt='2' then m.id end) as char),'')) as pinglun
from news n left join (select a.*,(select count(*) from commends b where a.news_id=b.news_id and a.hit>=b.hit) cnt from commends a  order by a.news_id asc,a.hit desc) m on n.id=m.news_id group by n.id limit 5;

结果如图：

转载于:https://my.oschina.net/houyiwujing/blog/38829