- — 禅道元数据大宽表关联
- — 以任务表为主表
- select
- zt_task.id as task_id , — 任务ID
- zt_task.project as project_id , — 项目ID
- zt_task.story as story_id , — 相关研发需求ID
- zt_task.name as task_name , — 任务名称
- zt_task.`type` as task_type , — 任务类型
- zt_task.pri as task_pri , — 任务优先级
- zt_task.estimate as task_estimate , — 任务最初预计
- zt_task.consumed as task_consumed , — 任务总计消耗
- zt_task.deadline as task_deadline , — 任务截止日期
- zt_task.status as task_status , — 任务状态
- concat(round(100-(zt_task.`left`/(zt_task.`left`+zt_task.consumed)*100),2),’%’) as task_jindu , — 任务进度
- zt_task.assignedTo as task_assignedTo, — 任务指派给
- zt_task.realStarted as task_realStarted, — 任务实际开始时间
- concat(year(zt_task.realStarted),’-‘,month(zt_task.realStarted)) as ssny , — 任务实际开始月份
- zt_task.finishedBy as task_finishedBy , — 任务由谁完成
- zt_task.finishedDate as task_finishedDate , — 任务实际完成时间
- (TIMESTAMPDIFF(day,zt_task.realStarted,zt_task.finishedDate)+1) duration , — 任务跨度天数
- zt_project.budget as project_budget , — 项目预算
- zt_project.name as project_name , — 项目名称
- zt_project.realBegan as project_realBegan , — 项目实际开始日期
- zt_project.realEnd as project_realEnd , — 项目实际完成日期
- zt_project.status as project_status , — 项目状态
- zt_project.progress as project_progress , — 项目进度
- zt_project.PO as project_PO , — 项目负责人
- zt_project.division as project_division, — 项目阶段类型
- zt_user.role as user_role, — 职位
- zt_user.realname as user_realname, — 姓名
- zt_user.score as user_score, — 积分
- zt_story.product as story_product, — 产品ID
- zt_story.title as story_title, — 研发需求名称
- zt_story.`type` as story_type , — 需求类型
- zt_story.pri as story_pri , — 需求优先级
- zt_story.estimate as story_estimate , — 需求预计工时
- zt_story.status as story_status , — 需求当前状态
- zt_story.stage as story_stage , — 需求所处阶段
- zt_story.openedBy as story_openedBy , — 需求由谁创建
- zt_story.openedDate as story_openedDate , — 需求创建日期
- zt_story.reviewedBy as story_reviewedBy , — 需求由谁评审
- zt_story.closedDate as story_closedDate , — 需求关闭日期
- zt_product.name as product_name , — 产品名称
- zt_product.draftStories as product_draftStories , — 草稿研发需求
- zt_product.activeStories as product_activeStories , — 激活研发需求
- zt_product.changingStories as product_changingStories , — 更改研发需求
- zt_product.reviewingStories as product_reviewingStories , — 评审中研发需求
- zt_product.finishedStories as product_finishedStories , — 已完成研发需求
- zt_product.closedStories as product_closedStories , — 已关闭研发需求
- zt_product.totalStories as product_totalStories, — 总研发需求数
- zt_bug.id as bug_id , — Bug编号
- zt_bug.title as bug_title , — Bug标题
- zt_bug.severity as bug_severity , — Bug严重程度
- zt_bug.`type` as bug_type , — Bug类型
- zt_bug.status as bug_status , — Bug状态
- zt_bug.feedbackBy as bug_feedbackBy , — Bug反馈者
- zt_bug.openedBuild as bug_openedBuild , — Bug影响版本
- zt_bug.assignedTo as bug_assignedTo , — Bug指派给
- zt_bug.deadline as bug_deadline , — Bug截止日期
- zt_case.id as case_id , — 用例编号
- zt_case.title as case_title , — 用例标题
- zt_case.pri as case_pri , — 用例优先级
- zt_case.`type` as case_type , — 用例类型
- zt_case.status as case_status , — 用例状态
- zt_case.openedDate as case_openedDate , — 用例创建日期
- zt_case.lastRunDate as case_lastRunDate , — 用例执行时间
- zt_case.lastRunner as case_lastRunner , — 用例执行人
- zt_case.openedBy as case_openedBy , — 用例创建人
- zt_case.lastRunResult as case_lastRunResult — 用例执行结果
- from zt_task — 任务明细表
- left join zt_project — 项目表
- on zt_task.project = zt_project.id
- left join zt_user — 用户表
- on zt_task.finishedBy = zt_user.account
- left join zt_story — 研发需求表
- on zt_task.story = zt_story.id
- and zt_task.storyVersion = zt_story.version
- left join zt_projectproduct
- on zt_project.id = zt_projectproduct.project
- left join zt_product
- on zt_projectproduct.product = zt_product.id
- left join zt_bug
- on zt_task.id = zt_bug.task
- left join zt_case
- on zt_bug.`case` = zt_case.id
- and zt_bug.caseVersion = zt_case.version
- order by zt_task.realStarted desc
作者 admin
张宴银，大数据开发工程师