如何在mysql中动态应用不同过滤条件

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')

这是表数据，我正在应用这个查询来为每个组仅获取4个数据，如果记录多于4个，则它显示为1，我从这个查询中获得以下输出

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  





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

我想应用过滤器使用动态where子句，首先我试图根据Id过滤事件，但我无法做到这一点，请帮助我哪里做错了这个查询，我试图实现

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  and  find_in_set( a.eventID,"1,2")

但是我无法获取数据，请告诉我我要去哪里wrong.Why我得到的是空白数据，而它应该给出该特定eventID记录1,2的记录

Answer 1

您还需要对子查询应用where子句：

SELECT a.*, 
       ( (SELECT COUNT(*)
          FROM eventlist AS c
          WHERE c.GroupID = a.GroupID AND c.eventID IN (1, 2)
         ) > 4
       ) AS ist
FROM eventlist AS a
WHERE (SELECT COUNT(*)
       FROM eventlist AS b 
       WHERE b.GroupID = a.GroupID AND b.eventID >= a.eventID AND
             b.eventID IN (1, 2)
      ) <= 4 AND
      a.eventID IN (1, 2); 

我还用in替换了find_in_set()。这通常是更好的方法，但如果您真的想要，也可以使用find_in_set()。