DELIMITER $$
DROP PROCEDURE IF EXISTS `ask`.`Managestatistics`$$CREATE DEFINER=`root`@`%` PROCEDURE `Managestatistics`(in city int,in id int)
BEGIN
DECLARE cityId int;
declare stopId int default 0;
drop table if exists tmp_table;
/*创建临时表*/
CREATE TEMPORARY TABLE tmp_table(
count_Question bigint,
count_IsNowledge bigint,
count_user bigint,
count_RoleId bigint,
count_City varchar(50)
);/*根据地区查询*/
if city > 0 then
declare cur CURSOR for SELECT Ask_CityId from ask_city;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stopId=1;
OPEN cur;
FETCH cur into cityId;
WHILE stopId <> 1 doinsert into tmp_table
(count_Question,count_IsNowledge,count_user,count_RoleId,count_City)
Values (
(select count(*) from Ask_Question as an
inner join ask_user ar
on an.Ask_Author=ar.Ask_UserId
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ay.Ask_CityId=cur
),(select count(*) from Ask_Question as
an inner join ask_user ar
on an.Ask_Author=ar.Ask_UserId
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where an.Ask_IsNowledge=1
and ay.Ask_CityId=cur),(select count(*) from ask_user ar
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ay.Ask_CityId=cur
),(select count(*) from ask_user ar
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ar.Ask_RoleId=1 or ar.Ask_RoleId=2
and ay.Ask_CityId=cur),(select Ask_CityName from ask_city where Ask_CityId=cur)
);FETCH cur into cityId;
end WHILE;
CLOSE cur;
end if;drop table if exists tmp_table;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `ask`.`Managestatistics`$$CREATE DEFINER=`root`@`%` PROCEDURE `Managestatistics`(in city int,in id int)
BEGIN
DECLARE cityId int;
declare stopId int default 0;
drop table if exists tmp_table;
/*创建临时表*/
CREATE TEMPORARY TABLE tmp_table(
count_Question bigint,
count_IsNowledge bigint,
count_user bigint,
count_RoleId bigint,
count_City varchar(50)
);/*根据地区查询*/
if city > 0 then
declare cur CURSOR for SELECT Ask_CityId from ask_city;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stopId=1;
OPEN cur;
FETCH cur into cityId;
WHILE stopId <> 1 doinsert into tmp_table
(count_Question,count_IsNowledge,count_user,count_RoleId,count_City)
Values (
(select count(*) from Ask_Question as an
inner join ask_user ar
on an.Ask_Author=ar.Ask_UserId
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ay.Ask_CityId=cur
),(select count(*) from Ask_Question as
an inner join ask_user ar
on an.Ask_Author=ar.Ask_UserId
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where an.Ask_IsNowledge=1
and ay.Ask_CityId=cur),(select count(*) from ask_user ar
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ay.Ask_CityId=cur
),(select count(*) from ask_user ar
inner join ask_city ay
on ar.Ask_CityId=ay.Ask_CityId
where ar.Ask_RoleId=1 or ar.Ask_RoleId=2
and ay.Ask_CityId=cur),(select Ask_CityName from ask_city where Ask_CityId=cur)
);FETCH cur into cityId;
end WHILE;
CLOSE cur;
end if;drop table if exists tmp_table;
END$$
DELIMITER ;
Execution Time : 00:00:00:015
Transfer Time : 00:00:01:079
Total Time : 00:00:01:094错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare cur CURSOR for SELECT Ask_CityId from ask_city;
declare CONTINUE HANDLER' at line 16Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
DROP PROCEDURE IF EXISTS `Managestatistics`$$CREATE PROCEDURE `Managestatistics`(IN city INT,IN id INT)
BEGIN
DECLARE cityId INT;
DECLARE stopId INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT Ask_CityId FROM ask_city;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stopId=1;
DROP TABLE IF EXISTS tmp_table;
/*创建临时表*/
CREATE TEMPORARY TABLE tmp_table(
count_Question BIGINT,
count_IsNowledge BIGINT,
count_user BIGINT,
count_RoleId BIGINT,
count_City VARCHAR(50)
);/*根据地区查询*/
IF city > 0 THENOPEN cur;
FETCH cur INTO cityId;
WHILE stopId <> 1 DOINSERT INTO tmp_table
(count_Question,count_IsNowledge,count_user,count_RoleId,count_City)
VALUES (
(SELECT COUNT(*) FROM Ask_Question AS an
INNER JOIN ask_user ar
ON an.Ask_Author=ar.Ask_UserId
INNER JOIN ask_city ay
ON ar.Ask_CityId=ay.Ask_CityId
WHERE ay.Ask_CityId=cur
),(SELECT COUNT(*) FROM Ask_Question AS
an INNER JOIN ask_user ar
ON an.Ask_Author=ar.Ask_UserId
INNER JOIN ask_city ay
ON ar.Ask_CityId=ay.Ask_CityId
WHERE an.Ask_IsNowledge=1
AND ay.Ask_CityId=cur),(SELECT COUNT(*) FROM ask_user ar
INNER JOIN ask_city ay
ON ar.Ask_CityId=ay.Ask_CityId
WHERE ay.Ask_CityId=cur
),(SELECT COUNT(*) FROM ask_user ar
INNER JOIN ask_city ay
ON ar.Ask_CityId=ay.Ask_CityId
WHERE ar.Ask_RoleId=1 OR ar.Ask_RoleId=2
AND ay.Ask_CityId=cur),(SELECT Ask_CityName FROM ask_city WHERE Ask_CityId=cur)
);FETCH cur INTO cityId;
END WHILE;
CLOSE cur;
END IF;DROP TABLE IF EXISTS tmp_table;
END$$
DELIMITER ;如果要根据city > 0,可以生成1个表,在游标中打开此表
这个已经运行通过.但是我才用CALL 存储过程传参的时候 还是会报错.
Unknown column 'cur' in 'where clause'
是什么意思?CUR是游标
DECLARE cur CURSOR FOR SELECT Ask_CityId FROM ask_city
这个Ask_CityId 是我想 循环一次根据游标的值来查询 所对应城市的值