正在从状态获取最新的行

Question

假设我有一个包含以下行的表

<1>//tr>

test_no	test_count_no	testrongtatus	test _run_no
1	0	停止
2	66	完成
3	67	完成
4	0		5	0	停止

一个相关的子查询符合以下条件：

select t.*
from test_table t
where t.test_no = (select t2.test_no
from test_table t2
where t2.test_run_no = t.test_run_no
order by (test_status = 'FINISH') desc,
(case when test_status = 'FINISH' then test_no end) desc,
test_no asc
);

order by有三个键可用于您的条件：

• 将'FINISH'放在第一位
• 对于'FINISH'，得到最大的test_no
• 对于其他人，选择最小的

这个逻辑也可以用row_number():来表示

select t.*
from (select t.*,
row_number() over (partition by test_run_no
order by (test_status = 'FINISH') desc,
(case when test_status = 'FINISH' then test_no end) desc,
test_no asc
) as seqnum
from test_table t
) t
where seqnum = 1;

以下语句如何：

select * from test t where t.test_status = 'FINISH'
and t.test_no = (select max(t1.test_no) from test t1
where t1.test_run_no = t.test_run_no)
union
select * from test t where t.test_status = 'STOP'
and not exists (select 1 from test t2 where t2.test_run_no = t.test_run_no
and t2.test_status = 'FINISH')
and t.test_no = (select min(test_no) from test t1
where t1.test_run_no = t.test_run_no);

它由两个SELECT部分组成。在第一部分中，对于具有FINISH状态的测试，选择测试编号最高的测试。第二部分为没有运行状态为FINISH的测试选择最小的测试编号。

这就是您想要的吗

SELECT MAX(test_table.test_run_no) FROM test_table 
where test_table.run_status = 'FINISH' and test_run_no=1 
GROUP BY test_table.test_no
IF @@ROWCOUNT = 0
BEGIN
select MAX(test_table.test_run_no) from test_table GROUP BY test_table.test_no
END

试试看：

select * from Table_3 where Table_3.test_count_no = (
select max(test_count_no) from Table_3
) and Table_3.test_status = 'FINISH' and Table_3.test_run_no = 1
union all
select x.test_no, x.test_count_no, x.test_status, x.test_run_no from
(select ROW_NUMBER() over (order by Table_3.test_run_no) as rownum, Table_3.* from Table_3 where Table_3.test_run_no = 2) as x where x.rownum = 1

使用order by desc和limit 1来获得确切的行。像这个

SELECT *
FROM test_table
WHERE test_table.test_status = 'FINISH'
ORDER BY test_no desc
LIMIT 1

如果你需要所有的行

SELECT *
FROM test_table
WHERE test_table.test_status = 'FINISH'
ORDER BY test_no desc

如果您需要test_no-only

SELECT test_table.test_no
FROM test_table
WHERE test_table.test_status = 'FINISH'
ORDER BY test_no desc
LIMIT 1