Jira统计工时sql备份

最新推荐文章于 2025-04-27 14:36:38 发布

原创最新推荐文章于 2025-04-27 14:36:38 发布 · 1.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#jira

本文提供了一种使用SQL查询从JIRA中提取工时汇总和工时明细的方法，包括项目名称、组名称、人员姓名、工时及任务执行时间等关键信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、工时汇总

SELECT pro.pname as 项目名称,gro.group_name as 组名称,ROUND(SUM(b.NUMBERVALUE),1) as 工时 FROM jiraissue a
LEFT JOIN customfieldvalue b on a.id=b.ISSUE and b.customfield=10600
LEFT JOIN customfieldvalue c on a.id=c.ISSUE and c.customfield=10700
LEFT JOIN app_user au on a.CREATOR=au.user_key
LEFT JOIN cwd_user usr on au.lower_user_name=usr.lower_user_name
LEFT JOIN cwd_membership mem on mem.child_id=usr.ID
LEFT JOIN cwd_group gro on mem.parent_id=gro.ID
LEFT JOIN project pro on a.project=pro.ID
WHERE
-- usr.active=1
-- AND gro.group_name='jira-developers'
-- AND a.PROJECT=11946
-- pro.pname in('')
-- AND
a.issuetype=3
AND c.DATEVALUE is not null
AND c.DATEVALUE BETWEEN '2019-08-01 00:00:00' AND '2019-08-31 00:00:00'
AND gro.group_name in('')
GROUP BY pro.ID,gro.group_name;

2、工时明细

-- 人员姓名、工时、任务执行时间
SELECT pro.pname as 项目名称,gro.group_name as 组名称,usr.display_name as 人员,ROUND(b.NUMBERVALUE,1) as 工时,c.DATEVALUE as 任务执行时间 FROM jiraissue a
LEFT JOIN customfieldvalue b on a.id=b.ISSUE and b.customfield=10600
LEFT JOIN customfieldvalue c on a.id=c.ISSUE and c.customfield=10700
LEFT JOIN app_user au on a.CREATOR=au.user_key
LEFT JOIN cwd_user usr on au.lower_user_name=usr.lower_user_name
LEFT JOIN cwd_membership mem on mem.child_id=usr.ID
LEFT JOIN cwd_group gro on mem.parent_id=gro.ID
LEFT JOIN project pro on a.project=pro.ID
WHERE
-- usr.active=1
-- AND gro.group_name='jira-developers'
-- AND a.PROJECT=11946
-- pro.pname in('市')
-- AND
a.issuetype=3
AND c.DATEVALUE is not null
AND c.DATEVALUE BETWEEN '2019-07-01 00:00:00' AND '2019-07-31 00:00:00'
AND gro.group_name in('')
ORDER BY pro.ID,gro.group_name,usr.display_name,c.DATEVALUE;