禅道常用查询

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';



`