如何使用自查询表从同一个表中获得最后执行的测试用例

Question

我正在使用MSSQL数据库Server 2008我有对数据库的查询

select *, DateDiff(n,StartDateTime,EndDateTime) as DateDifference 
from result 
where   Project='Sample'  AND SubProject='Sample' and  
(StartDateTime)>='11/01/2013 00:00:00' AND (EndDateTime)<='11/11/2014 23:59:59'
order by EndDateTime desc

它将列出结果表中的所有条目。以上查询的结果是

TestCaseName     userName               StartDatetime           EndDateTime 
poonam_tm_003   User1   2014-11-10 17:39:27.000 2014-11-10 17:39:31.000
poonam_tm_003   User1   2014-11-10 17:39:24.000 2014-11-10 17:39:27.000
poonam_tm_003   User1   2014-11-10 17:39:20.000 2014-11-10 17:39:24.000
poonam_tm_003   User2   2014-11-10 17:39:17.000 2014-11-10 17:39:20.000
30SepTestCase   TM1 2014-10-29 10:12:09.000 2014-10-29 10:17:07.000
30SepTestCase   TM1 2014-10-29 10:06:10.000 2014-10-29 10:09:41.000

我想要结婚者，比如最后一次执行的测试用例。

 TestCaseName        userName            StartDatetime           EndDateTime 
    poonam_tm_003   User1   2014-11-10 17:39:27.000 2014-11-10 17:39:31.000
    30SepTestCase   TM1 2014-10-29 10:12:09.000 2014-10-29 10:17:07.000

我只需要每个测试用例的最后一个项目。我指的是测试案例，其中有max(EndDateTime)

Answer 1

以下是一个可能有帮助的答案：

SELECT TestCaseName,
       userName,
       StartDatetime,
       EndDateTime,
       Datediff(N, StartDatetime, EndDateTime) AS datedifference
FROM   (SELECT *,
               ROW_NUMBER()
                 OVER (
                   partition BY testcasename
                   ORDER BY enddatetime DESC) AS rn
        FROM   yourtable) AS t
WHERE  Project = 'Sample'
       AND SubProject = 'Sample'
       AND ( StartDateTime ) >= '11/01/2013 00:00:00'
       AND ( EndDateTime ) <= '11/11/2014 23:59:59'
       AND rn = 1 

Answer 2

基于datediff()的使用，我推测您使用的是Server。如果是这样的话，您可以使用row_number()完成这一任务。

select r.*
from (select r.*, DateDiff(n, StartDateTime, EndDateTime) as DateDifference,
            row_number() over (partition by testcasename order by startdatetime desc) as seqnum
      from result r
      where Project = 'Sample' AND SubProject = 'Sample' and  
            StartDateTime >= '11/01/2013 00:00:00' AND EndDateTime <= '11/11/2014 23:59:59'
     ) r
where seqnum = 1;

Answer 3

我希望小组成员和马克斯一起工作

WITH CTE(Testcasename,userName,StartDatetime)
AS
(
SELECT Testcasename,userName,MAX(StartDatetime) StartDatetime
FROM result 
GROUP BY Testcasename,Username
)
SELECT A.*,DateDiff(DD,A.StartDateTime,A.EndDateTime) as DateDifference FROM 
RESULT A INNER JOIN CTE B ON A.Testcasename=B.Testcasename 
AND A.userName=B.userName
AND A.StartDatetime=B.StartDatetime
WHERE 
Project='Sample'  AND SubProject='Sample' and  
(A.StartDateTime)>='11/01/2013 00:00:00' AND (A.EndDateTime)<='11/11/2014 23:59:59'
order by A.EndDateTime desc