Oracle查询：如何在单个查询中进行有条件的获取

Question

我在Oracle数据库中有三个表，分别是newitems、itemdetails和ticketitems表。 以下是一些虚拟数据：

TICKETITEMS:

id    ticketid     itemid    quantity

1     100          9999      2
2     100          9998      5
3     100          2222      3

物品细节：

id    description    col_sumthing   extra_col
9999  Marlboro       val_sumthing   123_op
9998  Cigar Black    val_sumthing   456_pqwe

新项目：

id    description    col_sumthing
2222  100Pipes       val_different

最初，我只需要从itemdetails + ticketitems中获取数据，这很容易使用简单的连接语句。查询语句如下：

SELECT "TI".*, "I"."ID" AS "ITEMID", "I"."DESCRIPTION", "I"."col_sumthing" 
  FROM "TICKETITEMS" "TI"
 INNER JOIN "ITEMDETAILS" "I" ON TI.ITEMID = I.ID
 WHERE (TI.TICKET = '100')

这样的东西。

现在引入了新的item表，这个表可能也有一些在ticketitems表中存在的项目。

所以我想要一个结果，像这样：

Final Result:
id    description    itemid    quantity   col_sumthing    extra_col
1     Marlboro       9999      2          val_sumthing    123_op
2     Cigar Black    9998      5          val_sumthing    456_pqwe
3     100Pipes       2222      3          val_different   

我面临的问题是，只有在itemdetails中找不到详细信息时，才应该检查NEWITEMS。任何其他解决方法也可以接受。

- Aakash Sahai

Answer 1

SELECT TI.ID AS "id",
       COALESCE( I.DESCRIPTION, NI.DESCRIPTION ) AS "description"
       COALESCE( I.ID, NI.ID ) AS "itemid",
       TI.QUANTITY AS "quantity"
       COALESCE( I.COL_SUMTHING, NI.COL_SUMTHING ) AS "col_sumthing"
       I.EXTRA_COL AS "extra_col"
  FROM TICKETITEMS TI
  LEFT OUTER JOIN ITEMDETAILS I ON TI.ITEMID = I.ID
  LEFT OUTER JOIN NEWITEMS NI ON I.ID IS NULL AND TI.ITEMID = NI.ID
 WHERE TI.TICKETID = '100'

- dbenham

Answer 2

你可以使用两个OUTER JOIN：

SELECT ti.*, 
       nvl(i.id, n.id) itemid, 
       nvl(i.description, n.description) description, 
       nvl(i.col_sumthing, n.col_sumthing) col_sumthing 
  FROM ticketitems ti
  LEFT JOIN itemdetails i ON ti.itemid = i.id
  LEFT JOIN newsitems n ON ti.itemid = n.id
 WHERE ti.ticket = '100'

只要两个详细表都将ID作为主键，这个方法就可以奏效。如果你有超过两个详细表，请使用COALESCE。

- Vincent Malgrat

Answer 3

你需要使用ITEMDETAILS和NEWITEMS表的UNION，然后对数据进行排名并只选择相关行。

select 
  ti.id, 
  it.description, 
  it.id as itemid, 
  ti.quantity, 
  it.col_sumthing, 
  it.extra_col
from 
  ticketitem ti
  join (
    select 
      id,  
      description,  
      col_sumthing,  
      extra_col,  
      row_number() over (partition by id order by item_ordering) as item_priority
    from (
      select id, description, col_sumthing, extra_col, 1 as item_ordering
      from itemdetails
      union all
      select id, description, col_sumthing, null as extra_col, 2 as item_ordering
      from newitems 
    )
  ) it on (it.id = ti.itemid and it.item_priority = 1)

- Mike Meyers