如何修改我编写的查询以显示所需的结果？

Question

使用BOOK_ORDER、ORDER_ITEMS和BOOKS表，使用外部连接操作创建一个查询，该查询将列出books表中所有图书的书名、订购日期和订单号。按照ORDER_ITEMS.BOOKID的降序对您的输出进行排序。有三本从未排序过的图书应该会显示在列表的顶部。

我写了这个查询：

SELECT B.TITLE, BO.ORDERDATE, ORD.ORDERID FROM BOOKS B, BOOK_ORDER BO, ORDERITEMS ORD
WHERE B.BOOKID = ORD.BOOKID
AND BO.ORDERID = ORD.ORDERID(+)
ORDER BY ORD.ORDERID DESC

我得到了结果，但对问题的以下部分感到困惑：

“有三本从未订购过的图书应该会显示在您的列表顶部。”

我猜这意味着我需要显示那些Orderdate和OrderId为空或空白的Books。但是如何将这些行放在结果集的顶部呢？我应该在已编写的查询中更改哪些内容？

这是BOOKS表：-

Name                                      Null?    Type
----------------------------------------- -------- ---------------
BOOKID                                    NOT NULL NUMBER(15)
ISBN                                               VARCHAR2(10)
TITLE                                              VARCHAR2(30)
PUBDATE                                            DATE
PUBID                                              NUMBER(2)
COST                                               NUMBER(5,2)
RETAIL                                             NUMBER(5,2)
CATEGORY                                           VARCHAR2(12)

这是ORDER_ITEMS表：

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ORDERID                                   NOT NULL NUMBER(4)
 ITEMNUM                                   NOT NULL NUMBER(2)
 BOOKID                                    NOT NULL NUMBER(15)
 QUANTITY                                           NUMBER(3)

下面是BOOK_ORDER表的结构：

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ORDERID                                   NOT NULL NUMBER(4)
 CUSTOMERID                                         NUMBER(4)
 ORDERDATE                                          DATE
 SHIPDATE                                           DATE
 SHIPSTREET                                         VARCHAR2(20)
 SHIPCITY                                           VARCHAR2(20)
 SHIPSTATE                                          VARCHAR2(2)
 SHIPZIP                                            VARCHAR2(5)

Answer 1

试试这个：

SELECT B.TITLE,
       BO.ORDERDATE,
       ORD.ORDERID
FROM BOOKS B,
     BOOK_ORDER BO,
     ORDER_ITEMS ORD
WHERE B.BOOKID = ORD.BOOKID(+)
  AND BO.ORDERID(+) = ORD.ORDERID
ORDER BY ORD.ORDERID DESC

顺便说一句，IMHO，最好不要使用(+)运算符，而使用ANSI JOIN关键字：

SELECT B.TITLE,
       BO.ORDERDATE,
       ORD.ORDERID
FROM BOOKS B
LEFT OUTER JOIN ORDER_ITEMS ORD ON B.BOOKID = ORD.BOOKID
FULL OUTER JOIN  BOOK_ORDER BO ON BO.ORDERID = ORD.ORDERID
ORDER BY ORD.ORDERID DESC

here是一把小提琴

Answer 2

我认为这将显示未在顶部订购的书籍。

SELECT B.TITLE, BO.ORDERDATE, ORD.ORDERID FROM BOOKS B, BOOK_ORDER BO, ORDERITEMS ORD
WHERE B.BOOKID = ORD.BOOKID
AND BO.ORDERID = ORD.ORDERID(+)
ORDER BY nvl(ORD.ORDERID,0)

尝尝这个

Answer 3

也许这会有帮助

​

SELECT DISTINCT B.TITLE,
                (CASE
                  WHEN B.BOOKID = ORD.BOOKID THEN
                   BO.ORDERDATE
                  ELSE
                   NULL
                END) AS ORDERDATE,
                (CASE
                  WHEN B.BOOKID = ORD.BOOKID THEN
                   ORD.ORDERID
                  ELSE
                   NULL
                END) AS ORDERID
  FROM SO_BOOKS B, SO_BOOK_ORDER BO, SO_ORDER_ITEMS ORD
 WHERE (B.BOOKID = ORD.BOOKID AND BO.ORDERID = ORD.ORDERID)
    OR (B.BOOKID NOT IN
       (SELECT DISTINCT ORD.BOOKID FROM SO_ORDER_ITEMS ORD))
 ORDER BY ORDERID DESC

​