select zentao.zt_task.id,zentao.zt_task.name,sum(zentao.zt_taskestimate.consumed),zentao.zt_taskestimate.account from zentao.zt_taskestimate,zentao.zt_task where zentao.zt_taskestimate.task=zentao.zt_task.id group by zentao.zt_task.id
SELECT story FROM zentao.zt_project where name='2020-16'; 迭代表
SELECT * FROM zentao.zt_story
wher id
in(SELECT story FROM zentao.zt_project where name='2020-16'); story 表
#通过迭代查找story
SELECT * FROM zentao.zt_story
where zentao.zt_story.id in(SELECT story FROM zentao.zt_projectstory where project in (SELECT id FROM zentao.zt_project where name='2020-16'));
#通过迭代查找story
SELECT id,title,status FROM zentao.zt_story
where zentao.zt_story.id in
(SELECT story FROM zentao.zt_projectstory where project in (SELECT id FROM zentao.zt_project where name='2021-05-2')) and zentao.zt_story.deleted='0';
#通过迭代查找task
SELECT zentao.zt_task.id FROM zentao.zt_task where story in (SELECT story FROM zentao.zt_projectstory where project in (SELECT id FROM zentao.zt_project where name='2020-16'));
#工时计算
#项目 优先级 story 验收标准 分值 验收结果 备注 任务 预估 花费工时 剩余工时 责任人
#storyID story优先级 story标题 story描述 story状态 taskid task描述 task名字 task预估时间 task花费时间 task剩余时间 责任人
select a.story,
zentao.zt_story.pri,
zentao.zt_story.title as storytitle,
zentao.zt_story.title,
zentao.zt_story.status,
a.id as taskID,
a.name as taskdesc,
a.name as taskname,
a.estimate,
a.sumconsumed,
a.left,
a.account
from
(select zentao.zt_task.story,
(case
when zentao.zt_task.parent<1
then zentao.zt_task.id
Else zentao.zt_task.parent
end
) as id,
zentao.zt_task.name,
zentao.zt_task.estimate,
zentao.zt_task.consumed as tc,
zentao.zt_task.left,
zentao.zt_task.desc,
zentao.zt_taskestimate.task,
sum(zentao.zt_taskestimate.consumed) as sumconsumed,
zentao.zt_taskestimate.account
from zentao.zt_task left join zentao.zt_taskestimate on zentao.zt_taskestimate.task=zentao.zt_task.id
where
zentao.zt_task.project in (SELECT id FROM zentao.zt_project where name like'%2020-15%')
group by zentao.zt_task.id)a,zentao.zt_story,zentao.zt_storyspec
where a.story = zentao.zt_story.id=zentao.zt_storyspec.story order by a.story
#按BUGID查找
# id 简述 类型 状态 严重程度 问题创建人 解决人 解决方案 关闭日期 关联需求 关闭人
SELECT zentao.zt_bug.id,
zentao.zt_bug.title,
zentao.zt_bug.type,
zentao.zt_bug.status,
zentao.zt_bug.severity,
zentao.zt_bug.openedBy,
zentao.zt_bug.resolvedby,
zentao.zt_bug.resolution,
zentao.zt_bug.closedDate,
zentao.zt_story.title,
zentao.zt_bug.closedBy
FROM zentao.zt_bug left join zentao.zt_story on zentao.zt_bug.story=zentao.zt_story.id
where zentao.zt_bug.id in (1651,1232) ;
#按BUGID查找
SELECT zentao.zt_bug.id,
SELECT zentao.zt_bug.id,
(case zentao.zt_bug.resolvedBuild
when 12 then '重构版本'
when 13 then '库上最新白光版本'
else
zentao.zt_bug.resolvedBuild
end
) as resolvedBuild,
zentao.zt_bug.title,
zentao.zt_story.title,
(case zentao.zt_bug.severity
when 3 then '一般'
when 1 then '致命'
when 2 then '严重'
when 4 then '提示'
else
zentao.zt_bug.severity
end
) as severity,
(case zentao.zt_bug.status
when 'active' then 'Open'
when 'resolved' then 'Fixed'
else
zentao.zt_bug.status
end
) as status,
(case zentao.zt_bug.resolution
when 'duplicate' then '重复BUG'
when 'external' then '不是BUG'
when 'fixed' then '已解决'
when 'notrepro' then '无法重现 无法解决'
when 'willnotfix' then '不解决'
when 'delay' then '延期解决'
else
zentao.zt_bug.resolution
end
) as resolution,
(case zentao.zt_bug.type
when 'function' then '功能类'
when 'interface' then '界面类'
when 'performance' then '性能类'
when 'others' then '特殊类'
when 'advise' then '建议类'
when 'workflow' then '流程类'
when 'common' then '常识'
when 'data' then '信息类'
when 'accident' then '偶然类'
when 'sqldata' then '数据类'
else
zentao.zt_bug.resolution
end
) as type,
zentao.zt_bug.openedBy,
zentao.zt_bug.closedBy,
zentao.zt_bug.resolvedby,
zentao.zt_bug.closedDate
FROM zentao.zt_bug left join zentao.zt_story on zentao.zt_bug.story=zentao.zt_story.id
where zentao.zt_bug.id in (1651,1232) order by zentao.zt_bug.severity;
#按关闭时间查找(并且不是某个迭代里的)
SELECT zentao.zt_bug.id,
(case zentao.zt_bug.resolvedBuild
when 12 then '重构版本'
when 13 then '库上最新白光版本'
else
zentao.zt_bug.resolvedBuild
end
) as resolvedBuild,
zentao.zt_bug.title,
zentao.zt_story.title,
(case zentao.zt_bug.severity
when 3 then '一般'
when 1 then '致命'
when 2 then '严重'
when 4 then '提示'
else
zentao.zt_bug.severity
end
) as severity,
(case zentao.zt_bug.status
when 'active' then 'Open'
when 'resolved' then 'Fixed'
else
zentao.zt_bug.status
end
) as status,
(case zentao.zt_bug.resolution
when 'duplicate' then '重复BUG'
when 'external' then '不是BUG'
when 'fixed' then '已解决'
when 'notrepro' then '无法重现 无法解决'
when 'willnotfix' then '不解决'
when 'delay' then '延期解决'
else
zentao.zt_bug.resolution
end
) as resolution,
(case zentao.zt_bug.type
when 'function' then '功能类'
when 'interface' then '界面类'
when 'performance' then '性能类'
when 'others' then '特殊类'
when 'advise' then '建议类'
when 'workflow' then '流程类'
when 'common' then '常识'
when 'data' then '信息类'
when 'accident' then '偶然类'
when 'sqldata' then '数据类'
else
zentao.zt_bug.resolution
end
) as type,
zentao.zt_bug.openedBy,
zentao.zt_bug.closedBy,
zentao.zt_bug.resolvedby,
zentao.zt_bug.closedDate
FROM zentao.zt_bug left join zentao.zt_story on zentao.zt_bug.story=zentao.zt_story.id
where zentao.zt_bug.status='closed'
and zentao.zt_bug.closedDate>'2020-12-14 00:00:14' and zentao.zt_bug.closedDate<'2020-12-25 23:00:14'
and zentao.zt_bug.project not in (select zt_project.id from zt_project where zt_project.name like '%2020-16%')
order by zentao.zt_bug.severity ;
#按迭代查找
#id 影响版本 简述 关联需求 严重程度 状态 解决方案 类型 问题创建人 关闭人 解决人 关闭日期
SELECT zentao.zt_bug.id,
(case zentao.zt_bug.resolvedBuild
when 12 then '重构版本'
when 13 then '库上最新白光版本'
else
zentao.zt_bug.resolvedBuild
end
) as resolvedBuild,
zentao.zt_bug.title,
zentao.zt_story.title,
(case zentao.zt_bug.severity
when 3 then '一般'
when 1 then '致命'
when 2 then '严重'
when 4 then '提示'
else
zentao.zt_bug.severity
end
) as severity,
(case zentao.zt_bug.status
when 'active' then 'Open'
when 'resolved' then 'Fixed'
else
zentao.zt_bug.status
end
) as status,
(case zentao.zt_bug.resolution
when 'duplicate' then '重复BUG'
when 'external' then '不是BUG'
when 'fixed' then '已解决'
when 'notrepro' then '无法重现 无法解决'
when 'willnotfix' then '不解决'
when 'delay' then '延期解决'
else
zentao.zt_bug.resolution
end
) as resolution,
(case zentao.zt_bug.type
when 'function' then '功能类'
when 'interface' then '界面类'
when 'performance' then '性能类'
when 'others' then '特殊类'
when 'advise' then '建议类'
when 'workflow' then '流程类'
when 'common' then '常识'
when 'data' then '信息类'
when 'accident' then '偶然类'
when 'sqldata' then '数据类'
else
zentao.zt_bug.resolution
end
) as type,
zentao.zt_bug.openedBy,
zentao.zt_bug.closedBy,
zentao.zt_bug.resolvedby,
zentao.zt_bug.closedDate
FROM zentao.zt_bug left join zentao.zt_story on zentao.zt_bug.story=zentao.zt_story.id
where zentao.zt_bug.project in (select zt_project.id from zt_project where zt_project.name like '%2020-16%')
ORDER by zentao.zt_bug.status;
计算首天工时
SELECT sum(estimate) FROM zentao.zt_task
where zentao.zt_task.parent<1
and zentao.zt_task.project in (SELECT id FROM zentao.zt_project where name like'%2021-03%')
and zentao.zt_task.openedDate<'2021-02-23 00:00:00';
`
禅道常用查询
于 2020-12-16 15:24:47 首次发布