SQL/Inpla，如何更快、更高效地编写递增条件语句

Question

我希望有人能在这里帮助我。

我正在用黑斑羚写一个案例来查找表格中遗漏的行。

但是，我需要将下面的示例写为WHEN 1000=999。有没有一种更高效、更快、需要更少代码的解决方案，而不是写1000行CASE？这对我帮助很大。非常感谢。

CASE WHEN dif_tradecount = 2 THEN 1
WHEN dif_tradecount = 3 THEN 2
WHEN dif_tradecount = 4 THEN 3
WHEN dif_tradecount = 5 THEN 4
WHEN dif_tradecount = 6 THEN 5
WHEN dif_tradecount = 7 THEN 6
WHEN dif_tradecount = 8 THEN 7
WHEN dif_tradecount = 9 THEN 8
WHEN dif_tradecount = 10 THEN 9
WHEN dif_tradecount = 11 THEN 10
WHEN dif_tradecount = 12 THEN 11 .....
ELSE null
END AS missed_messages    

Answer 1

如果我得到了正确的任务：

CASE
WHEN dif_tradecount > 1 and dif_tradecount<=1000 THEN dif_tradecount-1
ELSE null
END AS missed_messages    

Answer 2

你可以做：

(case when dif_tradecount between 2 and 1000
then dif_tradecount - 1
end) as missed_messages

我想知道为什么case是必要的。也许：

nullif(dif_tradecount - 1, 0) as missed_messages

也会起作用。