sql-server - 具有 NULL 值的 FIRST_VALUE 和 LAST_VALUE

Question

我想使用 FIRST_Value 和 LAST_Value SQL Server 获得前 2 名昂贵的书和至少 2 种昂贵的书价格

Null 的存在给出了不正确的 Min 价格值，我希望 Min 价格忽略 Null

Select top 2 FIRST_VALUE(price) Over(Order by price) as MinPrice,
FIRST_VALUE(title) Over (order by price) as MinName,
LAST_VALUE(price) Over (order by price desc) as MaxPrice,
LAST_VALUE(title) over (Order by price desc) as MaxName
from titles; 

获取此输出

MINPrice    MINName                        Maxprice           MaxName
NULL       The Psychology of Computer        $22.95      But is it Friendly?
NULL       The Psychology of Computer        $21.59      Computer Phobic and 

我期望的结果应该在哪里

Minprice     MinName                        Maxprice          Maxname           
$2.99        The Gourmet Microwave           $22.95       But is it Friendly?
$2.99        You can Combat stress          $21.59       Computer Phobic and 

那么如何从 Min price 中消除 NULL

标签： sql-server

Answer 1

SELECT min(value) FROM table WHERE value IS NOT NULL.

它应该是这样的。