Where子句有while循环 [英] Where clause have while loop

问题描述

我有上面给出的程序，它显示语法错误。

我必须根据我的项目要求在where子句中使用while循环。



我必须比较where子句中的两个逗号分隔字段，所以我在where子句中设置了while循环，用于逐个获取逗号分隔值并传递它to find_in_set（）。



在用分号（;）关闭WHILE语句之前，但在查询中它是如何可能的......



这是因为它显示语法错误...有没有解决方法？

如果您有替代方案，请指导我...



提前谢谢



我尝试过：

I have upper given procedure and it display syntax error.
I have to use while loop in where clause as per my project requirement.

I have to compare two comma separated fields in where clause so i have set while loop in where clause which is used to get comma separated values one by one and pass it to find_in_set().

Before WHILE statement is closed with semicolon(;) but in query how it's possible...

This is the because it display syntax error... Is there any solution for this ??
If you have alternative for this so please guide me ...

Thank you in advance

What I have tried:

BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE ctr INT DEFAULT 0;
	DECLARE str_len INT;
	DECLARE total_row INT;
	DECLARE occurance INT;
	DECLARE split_comma text;
	DECLARE out_str TEXT DEFAULT '';
	DECLARE str TEXT DEFAULT '';
	DECLARE tmp_str TEXT DEFAULT '';
	DECLARE tmp_val VARCHAR(255) DEFAULT '';
	
	SELECT course AS str FROM student where s.id = studentid1;
	SET str_len = LENGTH(str);
	SET i = (LENGTH(str)-LENGTH(REPLACE(str, ',', '')))/LENGTH(',') + 1;  	
	
select (SELECT count(distinct t.id)
	FROM test As t, package AS p, packagetestassoc as pt, course AS c, student as s
	WHERE p.id = pt.packageid AND find_in_set(t.id, pt.testid) <> 0 AND 
	t.status = 1 AND find_in_set(c.id,p.courseid) AND 
	
	WHILE (ctr < i) DO
		SET ctr = ctr + 1;
		SET tmp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, ',',ctr), LENGTH(SUBSTRING_INDEX(str,',',ctr-1))+1),',','');
	 	SELECT find_in_set(tmp_str, p.courseid);
	END WHILE;
	
	AND s.id = studentid1 AND 
	(p.packagecost =0 or
 	p.id IN (SELECT ps.packageid FROM paymentstatus AS ps WHERE ps.studentid = studentid1)))
AS totaltest,

(select (SELECT count(distinct t.id)
	FROM test As t, package AS p, packagetestassoc as pt, course AS c, student as s
	WHERE p.id = pt.packageid AND find_in_set(t.id, pt.testid) <> 0 AND
	
	WHILE (ctr < i) DO
		SET ctr = ctr + 1;
		SET tmp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, ',',ctr), LENGTH(SUBSTRING_INDEX(str,',',ctr-1))+1),',','');
	 	SELECT find_in_set(tmp_str, p.courseid);
	END WHILE;
	
	AND s.id = studentid1 AND 
	t.status = 1 AND find_in_set(c.id,p.courseid) AND t.testtype = 2 AND CURDATE() > t.enddate AND
	 t.id NOT IN (SELECT testid FROM testresult WHERE studentid = studentid1) AND t.id NOT IN (SELECT testid FROM incompletedtest WHERE studentid = studentid1) AND (p.packagecost =0 or
 	p.id IN (SELECT ps.packageid FROM paymentstatus AS ps WHERE ps.studentid = studentid1))))
AS missedtest,

(SELECT count(*)
 FROM test AS t, incompletedtest AS i
 where i.studentid = studentid1 AND t.id = i.testid) AS
incompletetest,

(SELECT count(*)
 FROM test AS t, testresult AS i
 where i.studentid = studentid1 AND t.id = i.testid) AS
completetest;
END;

推荐答案

你需要分开一个将逗号分隔列表转换为表格的函数。



有很多例子。以下示例为您提供所需的一切：



在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

You need to separate off a function to convert the comma separated list into a table.

There are plenty of examples out there. The example below gives you all you need:

Using comma separated value parameter strings in SQL IN clauses[^]

这篇关于Where子句有while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！