示例仅供参考,请勿直接在生产环境运行,否则可能导致生产数据库负载异常!!! |
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'; |