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

(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;

结果如图：