日期:2014-05-16 浏览次数:20827 次
SELECT ctg_cname ,ctg_id FROM product_category WHERE ctg_id in (33,) UNION SELECT a.ctg_cname ,a.ctg_id FROM product_category a WHERE a.ctg_id=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=33 ) UNION SELECT b.ctg_cname ,b.ctg_id FROM product_category b WHERE b.ctg_id=(SELECT ctg_fatherid FROM product_category a WHERE a.ctg_id=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=33 )) UNION SELECT c.ctg_cname ,c.ctg_id FROM product_category c WHERE c.ctg_id=(SELECT ctg_fatherid FROM product_category b WHERE b.ctg_id=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=33 )) )
SELECT ctg_cname ,ctg_id FROM product_category WHERE ctg_id in (33, 父亲IDS,...)
// 发现递归 调用 貌似 mysql不支持 // 也了测试方法 调用失败 后放弃 DELIMITER $$ CREATE FUNCTION getPatherCategory (id INT,str char(20)) RETURNS CHAR(50) BEGIN DECLARE fid INT default -1; SET fid=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=id); IF fid > 0 THEN SET str=concat(str,',',fid,',',id); RETURN getPatherCategory(fid,str); ELSE SET str=concat(str,',',id); END IF; RETURN str; END $$
//改用如下方法 成功 //*_*! DELIMITER $$ CREATE FUNCTION getPatherCategory (id INT) RETURNS CHAR(255) BEGIN DECLARE fid INT default 1; DECLARE str CHAR(255) default id; WHILE id>0 DO SET fid=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=id); IF fid > 0 THEN SET str=concat(str,',',fid); SET id=fid; ELSE SET id=fid; END IF; END WHILE; RETURN str; END $$
show function status; drop function getPatherCategory select getPatherCategory(33)
DELIMITER $$ CREATE FUNCTION getPatherCategory (id INT) RETURNS CHAR(255) READS SQL DATA BEGIN DECLARE fid INT default 1; DECLARE str CHAR(255) default id; WHILE id>0 DO SET fid=(SELECT ctg_fatherid FROM product_category WHERE ctg_id=id); IF fid > 0 THEN SET str=concat(str,',',fid); SET id=fid; ELSE SET id=-1; END IF; END WHILE; RETURN str; END $$
SELECT ctg_cname ,ctg_id FROM product_category WHER