我尝试过一些, 但我无法找到解决办法, 不知怎的,我设法得到了这个结果。
以下是查询 :
DELIMITER ##
CREATE PROCEDURE test1(start_date DATE,end_date DATE)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a INT;
DECLARE present INT;
DECLARE total INT;
-- Declare the cursor
DECLARE id CURSOR
FOR
SELECT staff_id FROM ost_staff;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open the cursor
DROP TEMPORARY TABLE IF EXISTS reports;
CREATE TEMPORARY TABLE IF NOT EXISTS reports
(
staff_id INT(10),
present INT(10),
total INT(10)
);
OPEN id;
read_loop: LOOP
FETCH id INTO a;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO reports(staff_id,present,total)
SELECT (COUNT(I.interval_start)) AS present, DATEDIFF(DATE_ADD(end_date,INTERVAL 1 DAY),start_date) AS total
FROM effort_frequency E
RIGHT OUTER JOIN time_intervals I ON I.interval_start = E.log_date
AND E.staffid=a AND E.log_date BETWEEN start_date AND end_date
LEFT OUTER JOIN ost_holidays H ON H.holiday_date = I.interval_start
WHERE DATE_FORMAT(I.interval_start, %a ) = Sun OR H.holiday_date = I.interval_start OR E.total_effortspent IS NOT NULL;
-- Close the cursor
END LOOP;
CLOSE id;
END ##
我得出了以下结果:
+----------+-----------------+
| staff_id | present | total |
+----------+---------+-------+
| (NULL) | 23 | 24 |
| (NULL) | 22 | 24 |
+----------+---------+-------+
I m getting (NULL) for staff_id, How can I get the staff_id s there ? I tried using declared variable a in insert statement, but at that time I got only staff_id, I didn t get the other 2 fields, I can t get the staff_id from the select inside insert statement coz there is some problem.
现在我需要的是,我需要把变量a 的工作人员代号插入到临时表格中。
注意: 我对这个存储程序非常新, 但到这里为止, 有了管理, 如果能详细了解如何在“ 插入” 中使用“ 选择 ”, 包括这个解决方案, 就会更好 。