首页 新闻 会员 周边 捐助

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 | 小虾三级 | 园豆：1036
提问于：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
                        

                    

                        悟行
                        |
                        专家六级
                        |园豆：12559
                        |
                        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
                                |
                                园豆：1036
                                (小虾三级)
                                |
                                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




 
                            

                            

                                悟行
                                |
                                园豆：12559
                                (专家六级)
                                |
                                2018-02-01 11:15
                            

                        

                        

                            


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

                            

                                悟行
                                |
                                园豆：12559
                                (专家六级)
                                |
                                2018-02-01 11:28
                            

                        

                        

                            


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

                            

                                Supper_litt
                                |
                                园豆：1036
                                (小虾三级)
                                |
                                2018-02-01 12:33
                            

                        

            


            
        

    







        

                其他回答(2)
        

        

                

                    

                        
                        -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
        |
        园豆：10424
        (专家六级)
        |
        2018-01-31 11:19

    




                        


                        




    


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

    

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








                        


                        
                    

                

                

                    

                        
                        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 ;


 


希望对你有帮助!
                        


                        

                            


    

    

    

        魂心
        |
        园豆：99
        (初学一级)
        |
        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
        |
        园豆：1036
        (小虾三级)
        |
        2018-02-01 11:01
    








                        


                        
                    

                

        







    
    




清除回答草稿
    

           您需要登录以后才能回答，未注册用户请先注册。