mysql - 我希望当没有选择过滤器时返回所有带有计数的数据

Question

我正在创建一个商店，它将根据选定的过滤器运行，例如当传递studentid 时，它将返回带有记录计数的所有数据。当 schoolid 被传递时，它将返回所有关于 schoolid 的数据和记录数。同样，我希望当未选择任何过滤器时，返回所有包含记录数的数据。目前它显示错误“#1064 - 您的 SQL 语法有错误；请检查与您的 MariaDB 服务器版本对应的手册，以了解在 'ELSE 附近使用的正确语法

选择 SQL_CALC_FOUND_ROWS DISTINCTmap.idnstudent as Student_Count,ma' 在第 21 行”。请帮助我解决这个问题，我非常需要帮助。到目前为止我已经尝试了所有可能的解决方案。

DROP PROCEDURE IF EXISTS `TestspPendingTab_filter_all123`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestspPendingTab_filter_all123`(IN `no_filter_data` VARCHAR(500), IN `search_keyword` VARCHAR(50), IN `Stud_id_filter` VARCHAR(100), IN `sch_id_filter` VARCHAR(100), IN `Subj_id_filter` VARCHAR(100), IN `start_date_filter` DATE, IN `end_date_filter` DATE, OUT `recordfound` INT(10))
BEGIN

CASE WHEN no_filter_data = 'YES' THEN
SELECT SQL_CALC_FOUND_ROWS DISTINCT
map.idnstudent as Student_Count,map.idnstudent as student_id,usr.name as student_name,
map.idnsubject as Subject_Count,map.idnsubject as subject_id,sn.sub_name as subject_name,
s.id as School_Count,s.id as school_id,ifnull(s.schoolname,'N/A') AS schoolname
from `user` `usr` 
join `student_class_hour_map` `map` on `map`.`idnstudent`=`usr`.`id` and `usr`.`flag_status`=1
left join `subject_name` `sn` on `sn`.`id` = `map`.`idnsubject` 
left join `school_student_rel` `ssrl` on `ssrl`.`student_id`=`map`.`idnstudent` 
left join `school` `s` on `s`.`id` = `ssrl`.`school_id` 
WHERE 1
AND map.archivestatus=0 
AND recordfound = FOUND_ROWS()

ELSE 

SELECT SQL_CALC_FOUND_ROWS DISTINCT
map.idnstudent as Student_Count,map.idnstudent as student_id,usr.name as student_name,
map.idnsubject as Subject_Count,map.idnsubject as subject_id,sn.sub_name as subject_name,
s.id as School_Count,s.id as school_id,ifnull(s.schoolname,'N/A') AS schoolname    
from `user` `usr` 
join `student_class_hour_map` `map` on `map`.`idnstudent`=`usr`.`id` and `usr`.`flag_status`=1
left join `subject_name` `sn` on `sn`.`id` = `map`.`idnsubject` 
left join `school_student_rel` `ssrl` on `ssrl`.`student_id`=`map`.`idnstudent` 
left join `school` `s` on `s`.`id` = `ssrl`.`school_id` 

WHERE 1
AND map.archivestatus=0 
AND
CASE WHEN search_keyword != '' THEN
 (s.schoolname like CONCAT('%', search_keyword, '%') OR sn.sub_name like CONCAT('%', search_keyword, '%') OR usr.name like CONCAT('%', search_keyword, '%'))
ELSE
 1 
END  
AND
CASE WHEN Stud_id_filter != '' THEN
FIND_IN_SET(map.idnstudent,Stud_id_filter)
ELSE 1
END

AND 
CASE 
WHEN sch_id_filter != '' THEN
FIND_IN_SET(s.id,sch_id_filter)
ELSE
1
END 

AND 
CASE 
WHEN Subj_id_filter != '' THEN
FIND_IN_SET(map.idnsubject,Subj_id_filter)
ELSE
1
END

AND 
CASE 
WHEN start_date_filter != '' AND end_date_filter != '' THEN
map.startdate >= start_date_filter and map.enddate <= end_date_filter

ELSE
1
END
END

GROUP BY 
CASE WHEN stud_id_filter <> '' THEN map.idnstudent END,
CASE WHEN sch_id_filter <> '' THEN s.id END,
CASE WHEN subj_id_filter <> '' THEN map.idnsubject END;
SET recordfound = FOUND_ROWS();

END$$
DELIMITER ;

预期输出:当未选择任何过滤器时，它应该返回所有包含记录数的数据。如果选择任何过滤器，则根据该过滤器返回数据和记录数。

Answer 1

在表单的存储过程代码中

CASE
WHEN something
THEN query1;
ELSE query2;

查询后需要加分号。你没有它们。

您的代码有点令人困惑，因为您使用 CASE 来控制流和 SELECT 逻辑。是需要分号的控制流程/