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';