在尚未出价时允许highest_bid列为NULL的SQL查询

Question

对于学校，我需要在拍卖网站上做一个功能。为此，我需要在视图中加入两个表。这是很好的工作，直到我需要添加一个过滤器的价格范围。看起来很容易，但是查询结果需要在没有出价的情况下允许为NULL。

视图声明：

SELECT I.itemID, I.title, I.startPrice, B.highestBid, Cfi.category, I.endDate
FROM dbo.Items AS I INNER JOIN dbo.category_for_item AS Cfi ON V.itemID = Vir.itemID 
LEFT OUTER JOIN dbo.Bid AS B ON V.itemID = B.itemID

这将得到以下表格：

    itemID  title   startPrice  highestBid  category    endDate
1   1234    Alfa    25          26          PC          2018-09-22
2   1234    Alfa    25          NULL        PC          2018-09-22
3   5678    Bravo   9           20          Console     2018-07-03
4   5678    Bravo   9           15          Console     2018-07-03
5   5678    Bravo   9           NULL        Console     2018-07-03
6   9876    Charlie 84          100         Stamps      2018-06-14
7   9876    Charlie 84          90          Stamps      2018-06-14
8   9876    Charlie 84          85          Stamps      2018-06-14
9   9876    Charlie 84          NULL        Stamps      2018-06-14
10  1470    Delta   98          100         Fashion     2018-06-15
11  1470    Delta   98          99          Fashion     2018-06-15
12  1470    Delta   98          NULL        Fashion     2018-06-15
13  9631    Echo    56          65          Cars        2018-06-25
14  9631    Echo    56          NULL        Cars        2018-06-25
15  7856    Foxtrot 98          NULL        Dolls       2018-12-26

在环顾四周寻找答案后，我收到了一个查询，询问如何加入该视图，只显示最高的出价，而不是所有的出价：

SELECT VW.itemID, VW.title, VW.startPrice, VW.highestBid, VW.category, VW.endDate
FROM VW_SEARCH AS VW
   INNER JOIN (SELECT itemID, MAX(highestBid) AS MaxBid
                    FROM VW_SEARCH
                    GROUP BY itemID) VJ
        ON VW.itemID = VJ.itemID AND VW.highestBid = VJ.MaxBid

这给出了下一个结果：

    itemID  title   startPrice  highestBid  category    endDate
1   1234    Alfa    25          26          PC          2018-09-22
2   5678    Bravo   9           20          Console     2018-07-03
3   9876    Charlie 84          85          Stamps      2018-06-14
4   1470    Delta   98          100         Fashion     2018-06-15
5   9631    Echo    56          65          Cars        2018-06-25

正如我所预期的那样，结果只显示了至少有一个出价的项目。我尝试在subQuery上添加一个额外的条件，并加入右外，以确保我不会得到双倍的itemID。

SELECT VW.itemID, VW.title, VW.startPrice, VW.highestBid, VW.category, VW.endDate
FROM VW_SEARCH AS VW
   RIGHT OUTER JOIN (SELECT itemID, MAX(highestBid) AS MaxBid
                    FROM VW_SEARCH
                    WHERE highestBid > 0 OR highestBid IS NULL
                    GROUP BY itemID) VJ
        ON VW.itemID = VJ.itemID AND VW.highestBid = VJ.MaxBid

这给出了以下结果(没有添加结果5-1199，因为它与结果4完全相同，这将发生在实际表中，而不是上面的示例表中)：

         itemID title   startPrice  highestBid  category    endDate
1       1234    Alfa    25          26          PC          2018-09-22
2       5678    Bravo   9           20          Console     2018-07-03
3       9876    Charlie 84          85          Stamps      2018-06-14
4       NULL    NULL    NULL        NULL        NULL        NULL
1200    1470    Delta   98          100         Fashion     2018-06-15
1201    9631    Echo    56          65          Cars        2018-06-25

虽然这在技术上允许在列中使用NULL，但我需要在以下几个方面得到结果：

    itemID  title   startPrice  highestBid  catgory endDate
1   1234    Alfa    25          26          PC          2018-09-22
2   5678    Bravo   9           20          Console     2018-07-03
3   9876    Charlie 84          85          Stamps      2018-06-14
4   1470    Delta   98          100         Fashion     2018-06-15
5   9631    Echo    56          65          Cars        2018-06-25
6   7856    Foxtrot 98          NULL        Dolls       2018-12-26

我怎样才能得到想要的结果，还是根本不可能？另外，如果查询可以写得更好，请说出来。提前谢谢。

Answer 1

使用left join解决问题

SELECT VW.itemID, VW.title, VW.startPrice, VW.highestBid, VW.category, VW.endDate
FROM VW_SEARCH VW LEFT JOIN
     (SELECT itemID, MAX(highestBid) AS MaxBid
      FROM VW_SEARCH
      GROUP BY itemID
     ) VJ
     ON VW.itemID = VJ.itemID AND VW.highestBid = VJ.MaxBid;

或者，使用ANSI标准的ROW_NUMBER()函数：

select vw.*
from (select vw.*,
             row_number() over (partition by itemID
                                order by highestBid nulls last
                               ) as seqnum
      from vw_search vw
     ) vw
where seqnum = 1;

这保证了每项一行。

注意:并非所有数据库都支持NULLS LAST。这可能不是必要的，但您也可以使用case表达式来实现它。

Answer 2

你能至少给出风景的定义吗？也许表格的定义也是。

Answer 3

我只会使用subquery作为身份列：

select vw.*
from vw_search vw
where id = (select vm1.id
            from vw_search vm1
            where vm.itemID = vw1.itemID and vm1.highestBid is not null
            order by vm1.highestBid desc
            limit 1
           );

但是，有些DBMS不支持Server等LIMIT子句，如果支持，则可以使用TOP子句。