|
Sql代码 www.2cto.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
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.2cto.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
--将日期转换成年月日
SELECT DATE(NOW());
唯一的一个亮点应该就是统计延期任务数量的统计吧。
|