mysql - 在 HP ALM 的合并两个查询中需要帮助

Question

我在 HP ALM 中有两个不同的查询，但我想合并并将其合二为一。我不太擅长 SQL 查询，所以我在合并查询时遇到了困难。

查询 1:获取测试器的执行计数

Select
 TESTCYCL.TC_ACTUAL_TESTER as 'Tester',
 sum(case when TC_Status In('Blocked','Passed','Failed','Not Completed') then 1 else 0 end) as 'Total',
 sum(case when TC_Status = 'Passed' then 1 else 0 end) as 'Pass',
 sum(case when TC_Status = 'Failed' then 1 else 0 end) as 'Fail',
 sum(case when TC_Status = 'Blocked' then 1 else 0 end) as 'Blocked',
 sum(case when TC_Status In('Not Completed','Defferred','N/A') then 1 else 0 end) as 'Others'

From  TESTCYCL
Where
 TESTCYCL.TC_EXEC_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
 And
 TESTCYCL.TC_ACTUAL_TESTER in ('Username1')
 Group by TC_ACTUAL_TESTER

查询 2:让测试人员提出缺陷

SELECT
 BG_DETECTED_BY,
 Sum(case when BG_Status Not in ('Closed','Defect Resolved','Rejected')then 1 else 0 end) as 'Defect Raised'
 FROM BUG
 Where BUG.BG_DETECTED_BY in ('username1')
 AND BUG.BG_DETECTION_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
 Group by BG_DETECTED_BY

我试过 inner join/Left Join 但用户提出的缺陷计数不匹配

查询3:我试过的:

 Select
  TESTCYCL.TC_ACTUAL_TESTER as 'Tester',
  sum(case when TC_Status In('Blocked','Passed','Failed','Not Completed') then 1 else 0 end) as 'Total',
  sum(case when TC_Status = 'Passed' then 1 else 0 end) as 'Pass',
  sum(case when TC_Status = 'Failed' then 1 else 0 end) as 'Fail',
  sum(case when TC_Status = 'Blocked' then 1 else 0 end) as 'Blocked',
  sum(case when TC_Status In('Not Completed','Defferred','N/A') then 1 else 0 end) as 'Others',
  Sum(case when BG_Status Not in ('Closed','Defect Resolved','Rejected')then 1 else 0 end) as 'Defect Raised'

From  TESTCYCL 
Left Join BUG 
  on TESTCYCL.TC_ACTUAL_TESTER =  BUG.BG_DETECTED_BY 
 AND  BUG.BG_DETECTION_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
Where TESTCYCL.TC_EXEC_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
  And TESTCYCL.TC_ACTUAL_TESTER in ('username1')
Group by TC_ACTUAL_TESTER

Out 如下所述:

Expected Output:
Tester   Total Execution  Passed  Failed  ... Defect Raised
  A          5              3       2             10

Actual Output:
Tester   Total Execution  Passed  Failed  ... Defect Raised
  A          56              3       2             45

Answer 1

问题是您正在对乘积笛卡尔执行 COUNT()。而不是连接结果。

现在你正在做

   COUNT(A*B) instead of COUNT(A) || COUNT(B)

一般的例子，如果你有两个查询

SELECT * FROM A (ex: 10 rows)
SELECT * FROM B (ex: 10 rows)

你需要的是:

 SELECT temp1.*, temp2.*
 FROM (SELECT * FROM A) as temp1
 JOIN (SELECT * FROM B) as temp2
   ON temp1.ID = temp2.ID