首页新闻找找看学习计划

mysql 求一条sql( 按照某字段分组,每组取只20条数据,每个分组可能有100条,或者1,2条数据)

0
悬赏园豆:100 [已解决问题] 解决于 2018-02-01 13:36

RT.

1.按照某字段分组,

2.每组取只20条数据,

备注:每个分组可能有100条,或者1,2条数据。mysql不支持子查询limit.

最好是还是有点点效率。

问题补充:
create table task_test
(
    id int primary key auto_increment,
    user_name varchar(50) not null,
    ac_name varchar(50) not null,
    task_type tinyint not null,
    status tinyint not null,
    isdelete tinyint not null,
    start_time datetime not null,
    add_time datetime not null,
    update_time datetime not null
);

insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());

insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,3,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,4,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,3,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,4,0,now(),now(),now());

insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name3',1,3,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name4',1,4,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());

insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,2,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,2,0,now(),now(),now());

insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name2',1,0,0,now(),now(),now());
insert into task_test(user_name,ac_name, task_type, status, isdelete, start_time, add_time, update_time) values ('test2','name1',1,0,0,now(),now(),now());
mysql 创表和添加语句

测试表和语句,如上图。

 

ac_name 分组。

查询条件: status=0 and isdelete=0 and start_time<now()

Supper_litt的主页 Supper_litt | 小虾三级 | 园豆:974
提问于:2018-01-31 11:03
< >
分享
最佳答案
0
第一种方法:

SELECT
w.* FROM world w WHERE 20 > (SELECT count(*) FROM world WHERE world.continent = w.continent AND world.population < w.population) ORDER BY continent

world 表名
continent 分组字段
population 过滤分组的,最好用ID
20 每组取的条数

第二种方法(比第一种要慢一些):
SELECT a.*
FROM
  (
    SELECT
      w.*,
      (SELECT count(*) + 1
       FROM world
       WHERE world.continent = w.continent AND world.population < w.population ) AS group_id
    FROM world w
  ) a
WHERE a.group_id <= 20 order by continent

 

上面的SQL语句测试地址:(只需要把上面SQL语句复制到文本框执行就行了)
http://sqlzoo.net/wiki/SELECT_basics/zh


参考
例子地址:http://blog.csdn.net/ylqmf/article/details/39005949
收获园豆:100
再见2019 | 专家六级 |园豆:11616 | 2018-01-31 15:30

测试了一下,结果数量不对。我在题目中加入了一个测试样本。

测试的sql:(参考你的第一种)

SELECT w.*
FROM
task_test w
WHERE 20 >
(SELECT count(*)
FROM task_test
WHERE task_test.ac_name = w.ac_name AND task_test.id < w.id)
and w.`status`=0 and w.isdelete=0 and w.start_time<now()
ORDER BY ac_name

 

Supper_litt | 园豆:974 (小虾三级) | 2018-02-01 11:03

@Supper_litt: 把条件里面再放一遍试试

SELECT w.*
FROM
  task_test w
WHERE 20 >
      (SELECT count(*)
       FROM task_test
       WHERE task_test.ac_name = w.ac_name AND task_test.id < w.id AND
             task_test.`status`=0 AND task_test.isdelete=0 AND task_test.start_time<now())
      AND w.`status`=0 AND w.isdelete=0 AND w.start_time<now()
ORDER BY ac_name

 

再见2019 | 园豆:11616 (专家六级) | 2018-02-01 11:15

@Supper_litt: 这个改后的SQL语句,我测试过,可以

再见2019 | 园豆:11616 (专家六级) | 2018-02-01 11:28

@抽象ID: 好像可以呢,大兄弟。我再试试。

Supper_litt | 园豆:974 (小虾三级) | 2018-02-01 12:33
其他回答(3)
-1

类似实现mssql的partition over order by功能(category 分组字段,orderbycol 排序字段)

select x.* from tb x inner join (select a.category,a.orderbycol from tb a left join tb b on a.category=b.category and a.orderbycol<b.orderbycol group by a.category,a.orderbycol having count(b.orderbycol)<=20) y on x.category=y.category and x.orderbycol=y.orderbycol order by x.category,x.orderbycol;

Daniel Cai | 园豆:10374 (专家六级) | 2018-01-31 11:19

这个我最开始参考过的,不过不得行。。。

支持(0) 反对(0) Supper_litt | 园豆:974 (小虾三级) | 2018-02-01 11:05
-1

不支持子查询limit,
那就不能在子查询中每个不同字段返回前20个值,如果不在子查询中用limit,又不能在最外层用limit,因为返回的数目不确定,如果想在外层用,得算出每个不用的该字段值出现的次数,然后多于20的取20,其它的取真实值,相加做为limit的值,但还是不行,这只是知道结果的数目,但记录不是这样排列的,所以我觉得还是别用一条sql了,多写几条吧。

流年飞雨 | 园豆:1923 (小虾三级) | 2018-01-31 14:25

写一个存储过程吧

支持(0) 反对(0) 流年飞雨 | 园豆:1923 (小虾三级) | 2018-01-31 14:27

@一座城你说你不懂: 存储过程,,,其实现在互联网行业使用得真不多,修改麻烦,不容易调试。部署容易出错。版本不一致。等。

支持(0) 反对(0) Supper_litt | 园豆:974 (小虾三级) | 2018-02-01 11:04
0

实现以deptip分组,empid 排序,取前二十个sql:

 

select empid,deptid,salary,rank from (
select b.empid,b.deptid,b.salary,@rownum:=@rownum+1 ,
if(@pdept=b.deptid,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=b.deptid
from (
select empid,deptid,salary from heyf_t10 order by empid
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
having rank <20 ;

 

希望对你有帮助!

魂心 | 园豆:147 (初学一级) | 2018-01-31 17:47

我添加了,一个测试样本。sql在题目中。

测试了,但是结果好像多了。

select * from (
select b.*,@rownum:=@rownum+1 ,
if(@pdept=b.ac_name,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=b.ac_name
from (
select * from task_test where status=0 and isdelete=0 and start_time<now() order by start_time
) b ,(select @rownum :=0 , @pdept := null ,@rank:=0) c ) result
having rank <20 ;

支持(0) 反对(0) Supper_litt | 园豆:974 (小虾三级) | 2018-02-01 11:01
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册