禅道系统的一些统计语句

禅道系统的一些统计语句
 
Sql代码    www.zzzyk.com  
--统计信息  
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  
  
--任务列表    www.zzzyk.com  
  
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());  
 唯一的一个亮点应该就是统计延期任务数量的统计吧。

上一个：procedure emp_count
下一个：利用分析函数实现翻页