如何获取数据的顶部在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事件则只我们获取只有2记录

输出的愿望，我想只有4事件

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

OK，它的第一个更好的阅读中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