如何在mysql中获取前4条记录的数据

Question

CREATE TABLE `EventList` (
  `GroupID` int(11) NOT NULL ,

    `eventID` int(11) NOT NULL ,
  `EMPNAME` varchar(20) NOT NULL,
  `EMPAGE` int(11) NOT NULL,
  `SALARY` bigint(20) NOT NULL,
  `ADDRESS` varchar(20) NOT NULL,
  `empaddress` varchar(255) DEFAULT NULL,
  `file_data` tinyblob

) 


insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,2,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,5,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,7,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,8,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,9,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (2,15,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (2,16,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,19,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,22,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,24,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,27,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,29,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,31,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,32,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,33,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,34,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,35,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,36,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,37,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,39,"anil",5,556,'del','del','//document')

这是我对表结构的查询：

GroupID eventID EMPNAME EMPAGE  SALARY  ADDRESS empaddress  file_data
1          2    anil    5       556     del      del         ...
1          5    anil    5       556     del      del         ...
1          7    anil    5       556     del      del         ...
1          8    anil    5       556    del       del         ...
1          2    anil    5       556     del      del         ...
2          15   anil    5       556     del      del         ...
2          16   anil    5       556     del      del         ...
3          19   anil    5       556    del       del         ...
3          22   anil    5       556     del      del         ...
3          24   anil    5       556     del      del         ...
3          27   anil    5       556     del      del         ...
3          29   anil    5       556    del       del         ...
0          31   anil    5       556     del      del         ...
0          32   anil    5       556     del      del         ...
0          33   anil    5       556     del      del         ...
0          34   anil    5       556    del       del         ...
0          35   anil    5       556    del       del         ...
0          36   anil    5       556    del       del         ...
0          37   anil    5       556    del       del         ...

当前数据即将到来，我需要写查询来获取这样的数据，对于每个组，如果小于4个事件，我们只需要获取4个事件，那么我们只需要获取2个记录。

我想要的欲望输出

GroupID   eventID    EMPNAME    EMPAGE  SALARY  ADDRESS empaddress ist
    1          2    anil    5    556     del     del      ...      1
    1          5    anil    5    556     del     del      ...      1 
    1          7    anil    5    556     del     del      ...      1  
    1          2    anil    5    556     del     del      ...      1 
    2          15   anil    5    556     del     del      ...      0 
    2          16   anil    5    556     del     del      ...      0 
    3          19   anil    5    556     del     del      ...      1
    3          22   anil    5    556     del     del      ...      1 
    3          24   anil    5    556     del     del      ...      1
    3          29   anil    5    556     del     del      ...      1
    0          31   anil    5    556     del     del      ...      1 
    0          32   anil    5    556     del     del      ...      1 
    0          33   anil    5    556     del     del      ...      1 
    0          34   anil    5    556     del     del      ...      1

我想这样的记录，如果特别的组有超过4记录，那么应该是1，请建议我如何写这个查询，以便我可以得到这样的输出。

Answer 1

好的，正如我在评论中提到的，最好先阅读this question中提供的解决方案

但是将满足您的需求的查询如下所示：

SELECT a.*, 
  IF((SELECT COUNT(*) FROM eventlist AS c
      WHERE c.GroupID = a.GroupID) > 4, 1, 0) AS ist
FROM eventlist AS a
WHERE 
  (SELECT COUNT(*) FROM eventlist AS b 
  WHERE b.GroupID = a.GroupID and b.eventID >= a.eventID) <= 4
ORDER BY a.GroupID, a.eventID