禅道系统的一些统计语句
--统计信息SELECT u1.realname AS '姓名',CONCAT(SUM(t1.estimate),'小时') AS '最初预计',SUM(t1.consumed) AS '已消耗',COUNT(t1.isdelay) AS '延期任务数' FROM (SELECT t1.deadline,t1.status,t1.owner,t1.starttime,t1.estimate,t1.consumed,CASE WHEN (t1.deadline<DATE(NOW()) AND t1.status NOT IN ('done','cancel')) THEN '1'END AS 'isdelay'FROM zt_task t1 WHERE starttime>='2013-01-01' AND starttime<='2013-01-31' ) t1,zt_user u1 WHERE t1.owner=u1.account GROUP BY t1.OWNER--任务列表SELECT t1.id AS 'id',t1.name AS '名称',t1.starttime AS '开始时间',t1.deadline AS '结束时间',u1.realname AS '指派给',u2.realname AS '验收人'FROM zt_task t1,zt_user u1,zt_user u2 WHERE t1.owner=u1.account AND t1.checker=u2.account AND starttime>='2013-01-01' AND starttime<='2013-01-31' ORDER BY t1.owner--查询延期任务列表SELECT t1.id AS 'id',t1.name AS '名称',t1.starttime AS '开始时间',t1.deadline AS '结束时间',u1.realname AS '指派给',u2.realname AS '验收人'FROM zt_task t1,zt_user u1,zt_user u2 WHERE t1.owner=u1.account AND t1.checker=u2.account AND starttime>='2013-01-01' AND starttime<='2013-01-31' AND t1.status NOT IN ('done','cancel') AND t1.deadline<DATE(NOW()) ORDER BY t1.owner--将日期转换成年月日SELECT DATE(NOW());?唯一的一个亮点应该就是统计延期任务数量的统计吧。