mysql - 用于提取仅链接到已关闭缺陷的测试用例的 QC 查询

Question

我正在尝试从测试实验室中提取仅链接到已关闭、已拒绝和延迟缺陷的测试实例列表。测试实例可以链接到多个缺陷。在这种情况下，我必须检查所有链接的缺陷，如果任何缺陷未关闭/拒绝/推迟，则不应选择该实例。以下是我用来提取具有链接缺陷的所有测试实例的查询，但该查询也返回 Unresolved 缺陷。请帮助我改进查询以消除 Unresolved 缺陷。

Select
TESTCYCL.TC_TEST_ID as Test_ID,
TESTCYCL.TC_STATUS as TC_STATUS,
TESTCYCL.TC_EXEC_DATE As Actual_Execution_Date,
BUG.BG_BUG_ID as Defect_ID,  BUG.BG_USER_57  AS Project,
BUG.BG_STATUS as DEFECT_Status,
BUG.BG_USER_34 As Testing_Type,
BUG.BG_SEVERITY As Defect_Severity,
BUG.BG_USER_58 As Defect_Priority,
BUG.BG_DETECTION_DATE as Detection_Date



FROM BUG, TEST, V_LINK_TESTCYCL, TESTCYCL, CYCLE
WHERE BUG.BG_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
  AND TESTCYCL.TC_TESTCYCL_ID = V_LINK_TESTCYCL.LN_TESTCYCL_ID
  AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  AND CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID

//and (BUG.BG_STATUS='Closed' OR BUG.BG_STATUS='Deferred'  OR BUG.BG_STATUS='Rejected')

  order by   BUG.BG_BUG_ID

我得到的结果如下: Excel -1

我需要在结果中排​​除 1604，因为它与开放缺陷相关。如果有人能帮助我获得预期的结果，那就太好了。

谢谢，卡蒂克·S·S

Answer 1

根据您选择的发布周期，失败的测试已关闭缺陷(我在下面的 SQL 中有 162 个)

enter code hereSELECT BG_BUG_ID,
BG_STATUS,
TC_STATUS,
TEST.TS_RESPONSIBLE,
TS_NAME,
TS_STATUS

from (BUG LEFT JOIN LINK ON BG_BUG_ID = LN_BUG_ID )
LEFT JOIN V_LINK_TESTCYCL on LINK.LN_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
LEFT JOIN CYCLE ON LINK.LN_ENTITY_ID = CY_CYCLE_ID
LEFT JOIN TESTCYCL on V_LINK_TESTCYCL.LN_TESTCYCL_ID = TC_TESTCYCL_ID
left join test on tc_test_id = ts_test_id

Where

--BG_STATUS NOT IN ('Closed','Defect Resolved','Rejected')
BG_STATUS IN ('Closed','Cancel')
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
AND TC_STATUS NOT IN ('Passed')
and BG_DETECTED_IN_RCYC = '162'
ORDER BY BG_BUG_ID