这里是MySQL准备的声明
SELECT
ag.`attendance_type`,
ag.`description`,
COUNT(a.`attendance`) attendance_count
FROM
`ems_attendance` a
RIGHT JOIN `ems_att_group` ag
ON ag.`id` = a.`attendance`
AND a.`added_date` BETWEEN '2011-06-01'
AND '2011-06-17'
AND a.`users_id` = '9'
GROUP BY a.`attendance`
ORDER BY ag.`id`;和相应的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `users_attendance_report` $$
CREATE PROCEDURE `users_attendance_report` (
IN users_id INT,
IN start_date DATE,
IN end_date DATE
)
BEGIN
SELECT
ag.`attendance_type`,
ag.`description`,
COUNT(a.`attendance`) attendance_count
FROM
`ems_attendance` a
RIGHT JOIN `ems_att_group` ag
ON ag.`id` = a.`attendance`
AND a.`added_date` BETWEEN start_date
AND end_date
AND a.`users_id` = users_id
GROUP BY a.`attendance`
ORDER BY ag.`id` ;
END $$
DELIMITER;运行查询后,两者输出的结果相同。
Array
(
[0] => stdClass Object
(
[attendance_type] => present
[description] => Present
[attendance_count] => 10
)
[1] => stdClass Object
(
[attendance_type] => absent
[description] => Absent
[attendance_count] => 2
)
[2] => stdClass Object
(
[attendance_type] => other
[description] => Other
[attendance_count] => 0
)
[3] => stdClass Object
(
[attendance_type] => dayoff
[description] => Day Off
[attendance_count] => 2
)
)我仔细研究执行时间,两者都是一样的。何时何地比另一个更好更快?