基于某些标准限制记录的更好方法

Question

我尝试了一些不同的查询，以将结果集限制在我需要的范围内，但我一直缺少一些东西。

在我的示例中，我有三种文档类型：注册、标题和凭证。如果ASSET_ID有一条DOCTYPE=Title的记录，但也有另一条DOC_TYPE=Voucher的记录，那么我不想显示Voucher行。但是，如果ASSET_ID只有一个凭单行，那么我想显示该记录。第三个条件是，如果同一个asset_id有两个或多个凭证行，那么我只想显示一个。

以下是使用虚构数据的示例：

ASSET_ID    DOC_TYPE        State
101         Registration    TX
102         Title           AL
103         Title           NY
104         Title           CA
104         Voucher     
105         Title           CA
106         Voucher
106         Title           MD
107         Voucher
107         Voucher

我想到了两种方法。我的第一个想法是写一个查询，将凭单从结果中完全删除：

SELECT * FROM assets WHERE doc_type <> 'Voucher'

然后我要去UNION另一个查询，它只提取凭证文档类型：

SELECT asset_id, doc_type, state FROM assets WHERE doc_type <> 'Voucher'
UNION
SELECT * FROM (SELECT DISTINCT(asset_id), doc_type, state FROM assets WHERE 
doc_type = 'Voucher')

我最终所做的是编写union'ed查询来引用原始查询，以查找满足第一个查询中不包含的条件的值。但这感觉像是一种效率极低的编写查询的方法：

SELECT asset_id, doc_type, state FROM assets WHERE doc_type <> 'Voucher'
UNION
SELECT DISTINCT(asset_id), doc_type, state FROM assets WHERE doc_type = 
'Voucher' AND asset_id NOT IN (SELECT asset_id, doc_type, state FROM assets  
WHERE doc_type <> 'Voucher')

上面的方法是有效的，但我认为有一种比我提议的更干净的方法来处理这个问题。有什么建议吗？

Answer 1

也许是这样的？

SQL> with assets (asset_id, doc_type, state) as
2    (select 101, 'registration', 'tx' from dual union all
3     select 102, 'title'       , 'al' from dual union all
4     select 103, 'title'       , 'ny' from dual union all
5     select 104, 'title'       , 'ca' from dual union all
6     select 104, 'voucher'     , null from dual union all
7     select 105, 'title'       , 'ca' from dual union all
8     select 106, 'voucher'     , null from dual union all
9     select 106, 'title'       , 'md' from dual union all
10     select 107, 'voucher'     , null from dual union all
11     select 107, 'voucher'     , null from dual
12    ),
13  inter as
14    (select asset_id, doc_type, state,
15       row_number() over (partition by asset_id
16                          order by decode(doc_type, 'title', 1, 'voucher', 2)) rn
17     from assets
18    )
19  select asset_id, doc_type, state
20  from inter
21  where rn = 1
22  order by asset_id;
ASSET_ID DOC_TYPE     ST
---------- ------------ --
101 registration tx
102 title        al
103 title        ny
104 title        ca
105 title        ca
106 title        md
107 voucher
7 rows selected.
SQL>