I have two tables in sql. One is a table of test cases and the other is a table of test runs with a foreign key that links back to a test case. I want to get the most recent 10 test runs for each test case. I don't want to loop through if I don't have to, but I don't see any other way to solve this problem. What is the most effective way to handle this sort of thing in sql server?
我在sql中有两个表。一个是测试用例表,另一个是测试运行表,其中一个外键链接回测试用例。我想为每个测试用例获得最近10次测试运行。如果我不需要,我不想循环,但我没有看到任何其他方法来解决这个问题。在sql server中处理这类事情最有效的方法是什么?
3 个解决方案
#1
7
The idea:
想法:
select
...
from <test cases> as tc
outer apply (
select top 10 *
from <test runs> as tr
where
tr.<test case id> = tc.<id>
order by tr.<date time> desc
) as tr
or, if you just need to get data from table:
或者,如果您只需要从表中获取数据:
;with cte_test_runs as (
select
*,
row-Number() over(partition by <test case id> order by <date time> desc) as rn
from <test runs>
)
select *
from cte_test_runs
where rn <= 10
#2
3
You can use Row No. Use Inner of Left Join as the case may be..
您可以根据具体情况使用行号使用左内连接。
Select * from testCase a
left outer join
(Select Row_number() over (partition by testcase order by RecentDate desc ) RowNo, * from TestRuns) b
on a.pk = b.fk
where b.RowNo <=10
#3
1
You could use CROSS APPLY to select the top 10 per testCase ordered by date (or anyother ordering criteria).
您可以使用CROSS APPLY选择按日期排序的每个testCase的前10个(或任何其他订购标准)。
See the related question 'When should I use Cross Apply over Inner Join?' which explains it better than I can. https://*.com/a/1139231/1620715
请参阅相关问题“我应该何时使用Cross Apply over Inner Join?”这比我能解释得更好。 https://*.com/a/1139231/1620715
In your case:
在你的情况下:
SELECT * from TestCase T
CROSS APPLY
( SELECT TOP 10 * from TestRuns R where T.TestCaseId = R.TestCaseId
order by R.TestDate desc) TopResults
#1
7
The idea:
想法:
select
...
from <test cases> as tc
outer apply (
select top 10 *
from <test runs> as tr
where
tr.<test case id> = tc.<id>
order by tr.<date time> desc
) as tr
or, if you just need to get data from table:
或者,如果您只需要从表中获取数据:
;with cte_test_runs as (
select
*,
row-Number() over(partition by <test case id> order by <date time> desc) as rn
from <test runs>
)
select *
from cte_test_runs
where rn <= 10
#2
3
You can use Row No. Use Inner of Left Join as the case may be..
您可以根据具体情况使用行号使用左内连接。
Select * from testCase a
left outer join
(Select Row_number() over (partition by testcase order by RecentDate desc ) RowNo, * from TestRuns) b
on a.pk = b.fk
where b.RowNo <=10
#3
1
You could use CROSS APPLY to select the top 10 per testCase ordered by date (or anyother ordering criteria).
您可以使用CROSS APPLY选择按日期排序的每个testCase的前10个(或任何其他订购标准)。
See the related question 'When should I use Cross Apply over Inner Join?' which explains it better than I can. https://*.com/a/1139231/1620715
请参阅相关问题“我应该何时使用Cross Apply over Inner Join?”这比我能解释得更好。 https://*.com/a/1139231/1620715
In your case:
在你的情况下:
SELECT * from TestCase T
CROSS APPLY
( SELECT TOP 10 * from TestRuns R where T.TestCaseId = R.TestCaseId
order by R.TestDate desc) TopResults