Skip to end of metadata
Go to start of metadata
You are viewing an old version of this page. View the current version.
Compare with Current
View Page History
« Previous
Version 4
Next »
No files shared here yet.
示例
查询项目
GEARS为需要导出的项目的KEY值
SELECT ID,PKEY FROM project WHERE PKEY = 'GEARS'
查询项目下的问题
SELECT a.id,
b.pkey,
issuenum,
TIMEORIGINALESTIMATE,
TIMEESTIMATE,
TIMESPENT,
created,
updated,
creator
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS' AND a.project = b.id;
查询问题的字段数据
SELECT
j.REPORTER,
j.ASSIGNEE,
j.CREATOR,
j.SUMMARY,
j.DESCRIPTION,
j.CREATED,
j.UPDATED,
j.DUEDATE,
j.RESOLUTIONDATE,
j.COMPONENT,
cf.cfname,
cf.CUSTOMFIELDSEARCHERKEY,
cfv.CUSTOMFIELD,
cfv.STRINGVALUE,
cfv.TEXTVALUE,
cfv.NUMBERVALUE,
cfv.DATEVALUE,
cfv.VALUETYPE
FROM
jiraissue j
LEFT JOIN customfieldvalue cfv ON j.ID = cfv.ISSUE
LEFT JOIN customfield cf ON cfv.CUSTOMFIELD = cf.ID
WHERE
j.issuenum = 8 -- 比如issuekey=GEARS-8,这里的issuenum=8
AND j.PROJECT = 10001 --项目id;
查询评论信息
SELECT
*
FROM
jiraaction aa
WHERE
aa.issueid IN (
SELECT
a.id
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS'
AND a.project = b.id);
查询附件
SELECT
*
FROM
fileattachment aa
WHERE
aa.issueid IN (
SELECT
a.id
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS'
AND a.project = b.id)
查询工时日志
SELECT
*
FROM
worklog aa
WHERE
aa.issueid IN (
SELECT
a.id
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS'
AND a.project = b.id)
查询变更历史
变更历史
SELECT
*
FROM
changegroup aa
WHERE
aa.issueid IN (
SELECT
a.id
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS'
AND a.project = b.id
)
变更详情
SELECT
*
FROM
changeitem bb
WHERE
bb.groupid IN (
SELECT
id
FROM
changegroup aa
WHERE
aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id )
)
链接关系
基础数据
SELECT * FROM issuelinktype
业务数据
SELECT * FROM issuelink aa WHERE source IN(
SELECT a.id FROM jiraissue a,project b WHERE b.PKEY = 'GEARS'
AND a.project = b.id
)
UNION
SELECT * FROM issuelink aa WHERE destination IN(
SELECT a.id FROM jiraissue a,project b WHERE b.PKEY = 'GEARS'
AND a.project = b.id
)
关注和投票
SELECT
*
FROM
userassociation aa
WHERE
aa.`SINK_NODE_ENTITY` = 'Issue'
AND aa.`ASSOCIATION_TYPE` = 'WatchIssue'
AND aa.sink_node_id IN (
SELECT
a.id
FROM
jiraissue a,
project b
WHERE
b.PKEY = 'GEARS'
AND a.project = b.id
)
人员关系对照表
SELECT bb.`lower_user_name`,bb.`user_key`,aa.`email_address`,aa.`display_name` FROM cwd_user aa,app_user bb
WHERE aa.`lower_user_name` = bb.`lower_user_name`