测试工具对比：Jira与禅道数据库分析详解

方便你写周报、写总结、出报告
1.

测试人员发现有效bug数排行。条件：时间
1.

SELECT
	COUNT(*) num,
	openedBy,
	realname
FROM
	zt_bug b
INNER JOIN zt_user u ON u.account = b.openedBy
WHERE
	DATE_FORMAT(b.openedDate, '%Y%m') = '202003'
AND b.resolution NOT IN (
	'bydesign',
	'duplicate',
	'external',
	'notrepro',
	'willnotfix'
)
GROUP BY
	b.openedBy
ORDER BY
	num DESC;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

研发人员发生bug数排行。条件：时间
1.

SELECT
	COUNT(*) num,
	openedBy,
	realname
FROM
	zt_bug b
INNER JOIN zt_user u ON u.account = b.resolvedBy
WHERE
	DATE_FORMAT(b.openedDate, '%Y%m') = '202304'
GROUP BY
	b.resolvedBy
ORDER BY
	num DESC;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

研发Bug被激活次数(非一次性通过)。 条件：时间
1.

SELECT
	SUM(num) num,
	realname
FROM
	(
		SELECT
			b.id,
			COUNT(1) num,
			u.realname realname
		FROM
			zt_bug b
		INNER JOIN zt_action a ON a.objectID = b.id
		INNER JOIN zt_user u ON u.account = b.resolvedBy
		WHERE
			DATE_FORMAT(b.openedDate, '%Y%m') = '201204'
		AND a.objectType = 'bug'
		AND a.action = 'activated'
		GROUP BY
			b.id
		ORDER BY
			num DESC
	) tem
GROUP BY
	tem.realname
ORDER BY
	num DESC;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

统计某时间段内每个人在每个项目中的工作量
1.

SELECT
	p.pname,
	u.lower_user_name username,
	sum(l.timeworked / 28800) all_time
FROM
	jiraissue ji
INNER JOIN project p ON ji.PROJECT = p.ID
INNER JOIN worklog l ON ji.id = l.issueid
INNER JOIN app_user u ON l.UPDATEAUTHOR = u.user_key
WHERE
	l.startdate BETWEEN '2020-03-01'
AND '2020-03-31 23:59:59'
GROUP BY
	l.UPDATEAUTHOR,
	p.id
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

如果统计bug问题数，以jiraissue表展开sql编写即可
1.