1、查询所有需求项
-- This query shows all requirements in all folders.
SELECT
REQ.RQ_REQ_ID AS 'Requirement ID',
REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco
REQ.RQ_REQ_NAME AS 'Requirement Name',
REQ.RQ_REQ_COMMENT AS 'Requirement Description',
REQ_TYPE.TPR_NAME AS 'Requirement Type',
REQ.RQ_USER_03 AS 'Risk'
FROM
REQ
JOIN REQ_TYPE
ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"
WHERE
TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"
ORDER BY
RQ_REQ_ID
2、查询所有用例
-- This query shows all test cases in all folders.
SELECT
TEST.TS_TEST_ID AS 'Test ID',
TEST.TS_NAME AS 'Test Name',
TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM
TEST
ORDER BY
TEST.TS_TEST_ID
3、查询用例与需求映射（哪些需求被用例覆盖）
-- This query shows all requirements with any test cases that are mapped to the requirement.
-- If there are 2 test cases mapped to a requirement, then both will be included.
SELECT
REQ.RQ_REQ_ID AS 'Requirement ID',
REQ.RQ_USER_01 AS 'Req ID Project', -- this has the original REQ ID from project doco
REQ.RQ_REQ_NAME AS 'Requirement Name',
REQ.RQ_REQ_COMMENT AS 'Requirement Description',
REQ_TYPE.TPR_NAME AS 'Requirement Type',
REQ.RQ_USER_03 AS 'Risk',
TEST.TS_TEST_ID AS 'Test Case ID',
TEST.TS_NAME AS 'Test Case Name',
TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM
REQ
LEFT JOIN
REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID -- join requirements to test cases (REQ to REQ_COVER)
LEFT JOIN
TEST ON REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID -- join requirements to test cases (REQ_COVER to TEST)
JOIN
REQ_TYPE ON REQ.RQ_TYPE_ID = REQ_TYPE.TPR_TYPE_ID -- join to get the name of the requirement type e.g "Folder"
WHERE
REQ_TYPE.TPR_NAME != 'Folder' -- Only retrieve requirements of type "Business" or "Functional"
ORDER BY
REQ.RQ_REQ_ID
