问对四行sql进行分组。

EN

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,company_id VARCHAR(12) NOT NULL
,created_at DATE NOT NULL
);

INSERT INTO my_table VALUES
( 1123,'abcd','2015/10/12'),
( 8291,'abcd','2015/10/14'),
( 9012,'abcd','2015/10/15'),
( 9540,'abcd','2015/10/16'),
(10342,'abcd','2015/10/21'),
(10456,'abcd','2015/10/22'),
( 2301,'efgh','2015/10/13'),
( 4000,'efgh','2015/11/01'),
( 4023,'efgh','2015/11/03'),
( 6239,'efgh','2015/11/08'),
( 7500,'efgh','2015/11/14');

SELECT x.* 
  FROM my_table x 
  JOIN my_table y 
    ON y.company_id = x.company_id 
   AND y.created_at <= x.created_at 
 GROUP 
    BY x.id 
HAVING COUNT(*) <= 4 
 ORDER 
    BY company_id
     , created_at;
+------+------------+------------+
| id   | company_id | created_at |
+------+------------+------------+
| 1123 | abcd       | 2015-10-12 |
| 8291 | abcd       | 2015-10-14 |
| 9012 | abcd       | 2015-10-15 |
| 9540 | abcd       | 2015-10-16 |
| 2301 | efgh       | 2015-10-13 |
| 4000 | efgh       | 2015-11-01 |
| 4023 | efgh       | 2015-11-03 |
| 6239 | efgh       | 2015-11-08 |
+------+------------+------------+

SELECT a.id
     , a.company_id
     , a.created_at
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = x.company_id THEN @i:=@i+1 ELSE @i:=1 END i, @prev:=x.company_id prev 
         FROM my_table x
            , (SELECT @i:=1,@prev:=null) vars 
        ORDER 
           BY x.company_id
            , x.created_at
     ) a
 WHERE i <= 4;

select company_id, 
    min(created_at) as created_at_1,
    (select created_at from t where company_id=t1.company_id order by created_at limit 1 offset 1) as created_at_2,
    (select created_at from t where company_id=t1.company_id order by created_at limit 1 offset 2) as created_at_3,
    (select created_at from t where company_id=t1.company_id order by created_at limit 1 offset 3) as created_at_4
from t as t1
group by company_id

select company_id, 
    min(created_at) as created_at_1,
    min(case r when 2 then created_at else null end) as created_at_2,
    min(case r when 3 then created_at else null end) as created_at_3,
    min(case r when 4 then created_at else null end) as created_at_4
from (
    select company_id, created_at,
        (case company_id when @curType 
        then @curRank := @curRank + 1
        else @curRank := 1 and @curType := company_id end)+1 as r
    from t, (select @curRank := 0, @curType := '') f
    order by company_id, created_at
) as o
where r <= 4
group by company_id

   SELECT S.company_id,
          A.created_at created_at_1,
          B.created_at created_at_2,
          C.created_at created_at_3,
          D.created_at created_at_4
     FROM sample S
LEFT JOIN sample A on S.company_id = A.company_id AND A.id NOT IN(S.id)
LEFT JOIN sample B on S.company_id = B.company_id AND B.id NOT IN(S.id, A.id)
LEFT JOIN sample C on S.company_id = C.company_id AND C.id NOT IN(S.id, A.id, B.id)
LEFT JOIN sample D on S.company_id = D.company_id AND D.id NOT IN(S.id, A.id, B.id, C.id)
 GROUP BY S.company_id