我在HP ALM(正式为Quality Center)中运行两个查询:
查询1:
SELECT TEST.TS_NAME FROM CYCLE JOIN TESTCYCL ON (TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID) JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID) WHERE CYCLE.CY_CYCLE_ID = 44451
这将返回38行,其中包含我要报告的所有测试名称。
查询2:
SELECT STEP.ST_RUN_ID as "RunId" /*Test Step.Run No*/ , TEST.TS_NAME as "Test Name", STEP.ST_STATUS as "Run Status", STEP.ST_STEP_NAME as "Step Name", CYCLE.CY_CYCLE as "TestSet", CYCL_FOLD.CF_ITEM_NAME as "Test Lab Folder Name" FROM RUN, CYCL_FOLD, CYCLE, STEP, TEST WHERE RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID AND CYCLE.CY_CYCLE_ID = 44451 AND STEP.ST_RUN_ID = RUN.RN_RUN_ID AND RUN.RN_TEST_ID = TEST.TS_TEST_ID AND RUN.RN_RUN_ID in (select MAX(RUN.RN_RUN_ID) FROM RUN GROUP BY RN_TESTCYCLE_ID)
该查询返回所有测试以及各个步骤及其状态。MAX语句返回该测试的最新运行。
运行测试时,将在STEP表中分配RUN_ID。问题是,如果尚未运行测试,则该测试将没有RUN_ID,因此不会包含在结果中。
因此,我创建了以下查询3:
SELECT STEP.ST_RUN_ID as "RunId" /*Test Step.Run No*/, TEST.TS_NAME as "Test Name", STEP.ST_STATUS as "Run Status", STEP.ST_STEP_NAME as "Step Name", CYCLE.CY_CYCLE as "TestSet", CYCL_FOLD.CF_ITEM_NAME as "Test Lab Folder Name" FROM RUN, CYCL_FOLD, CYCLE, STEP, TEST RIGHT JOIN ( SELECT TEST.TS_NAME FROM CYCLE JOIN TESTCYCL ON (TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID) JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID) WHERE CYCLE.CY_CYCLE_ID = 44451) alltest ON alltest.TS_NAME = TEST.TS_NAME WHERE RUN.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID AND CYCLE.CY_FOLDER_ID = CYCL_FOLD.CF_ITEM_ID AND STEP.ST_RUN_ID = RUN.RN_RUN_ID AND RUN.RN_TEST_ID = TEST.TS_TEST_ID AND RUN.RN_RUN_ID in (select MAX(RUN.RN_RUN_ID) FROM RUN GROUP BY rn_testcycl_id)
我想对所有测试进行RIGHT JOIN并填充记录有运行的行,但仍不返回NULL行。运行查询2或3没有区别。
它不返回行,因为该where子句将它们过滤掉。
where
使用ANSI标准join语法(一个on子句),然后将条件放入该on子句中。结果是这样的:
join
on
SELECT s.ST_RUN_ID as "RunId" /*Test Step.Run No*/, t.TS_NAME as "Test Name", s.ST_STATUS as "Run Status", s.ST_STEP_NAME as "Step Name", c.CY_CYCLE as "TestSet", cf.CF_ITEM_NAME as "Test Lab Folder Name" FROM RUN r join CYCL_FOLD cf on c.RN_CYCLE_ID = cf.CY_CYCLE_ID join CYCLE c on c.CY_FOLDER_ID = cf.CF_ITEM_ID join STEP s on s.ST_RUN_ID = r.RN_RUN_ID join TEST t on r.RN_TEST_ID = t.TS_TEST_ID right join (SELECT TEST.TS_NAME FROM CYCLE JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID WHERE CYCLE.CY_CYCLE_ID = 44451 ) alltest ON alltest.TS_NAME = t.TS_NAME and r.RN_RUN_ID in (select MAX(RUN.RN_RUN_ID) FROM RUN GROUP BY rn_testcycl_id);