Mysql查询以获取每种类型的有限行 [英] Mysql query to fetch limited rows of each type

问题描述

我在MYSql中有一个表叫做公司，每个公司都有类型1，类型2类型3，

I have a Table in MYSql called companies and each companies has a type say type 1, type 2 type 3,

示例：

id   company_name   company_type
===============================
1    test1          3
2    xyz            2
3    ashdasdjk      2 
4    test 4         1 
5    test           3 
6    ahsdkjsg       1
7    TCS            2
and so on ...

现在我想写一个查询获取结果，我得到20公司类型1，20公司类型2和20公司类型3 ...我的意思是我想获取最多20公司的每种类型

now i want to write a query to fetch results such that i get 20 companies of type 1, 20 companies of type 2 and 20 companies of type 3... i mean i want to fetch maximum of 20 companies of each type

我使用Codeigniter ..

I am using Codeigniter..

推荐答案

select * from (
    select
    c.*,
    @rn := if(company_type != @ct, 1, @rn + 1) as rownumber,
    @ct := company_type
    from
    companies c
    , (select @rn := 0, @ct := null) var_init
    order by
    company_type
) comp
where rownumber <= 20;

这篇关于Mysql查询以获取每种类型的有限行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！