时间戳

首页 > 解决方案 > 我想在没有选择过滤器时返回所有带有计数的数据

mysql - 我想在没有选择过滤器时返回所有带有计数的数据

问题描述

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

SELECT SQL_CALC_FOUND_ROWS DISTINCT map.idnstudent as Student_Count,ma' at line 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 ;

预期输出：当没有选择过滤器时，它应该返回所有记录数的数据。如果选择了任何过滤器，则根据该过滤器返回数据以及记录数。

标签： mysqlstored-proceduresmariadbmysql-error-1064case-statement

解决方案

在表单的存储过程代码中

CASE
WHEN something
THEN query1;
ELSE query2;

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

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

推荐阅读