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 6
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.CUSTOMFIELDTYPEKEY,
cfv.CUSTOMFIELD,
cfv.STRINGVALUE,
cfv.TEXTVALUE,
cfv.NUMBERVALUE,
cfv.DATEVALUE,
cfv.VALUETYPE ,
cfo.customvalue
FROM
jiraissue j
LEFT JOIN customfieldvalue cfv ON j.ID = cfv.ISSUE
LEFT JOIN customfield cf ON cfv.CUSTOMFIELD = cf.ID
LEFT JOIN customfieldoption cfo on cfv.STRINGVALUE=cfo.ID
WHERE
j.issuenum = 8 -- 比如issuekey=DEMO-8,这里的issuenum=8
AND j.PROJECT = 10001;
查询评论信息
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`
模块
SELECT
na.SOURCE_NODE_ID as "问题id",
na.SINK_NODE_ID as "模块id",
c.cname as "模块名称"
FROM
nodeassociation na
LEFT JOIN component c on na.SINK_NODE_ID=c.ID
WHERE
SOURCE_NODE_ID = 10939
AND SINK_NODE_ENTITY = 'Component';