禅道系统的一些统计语句

--统计信息
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());

 唯一的一个亮点应该就是统计延期任务数量的统计吧。