如何在不重复的情况下重用大型查询? [英] How to reuse a large query without repeating it?
问题描述
如果我有两个查询,分别称为horrible_query_1
和ugly_query_2
,我想对它们执行以下两个减号操作:
If I have two queries, which I will call horrible_query_1
and ugly_query_2
, and I want to perform the following two minus operations on them:
(horrible_query_1) minus (ugly_query_2)
(ugly_query_2) minus (horrible_query_1)
或者我有一个terribly_large_and_useful_query
,它产生的结果集我想用作以后的几个查询的一部分.
Or maybe I have a terribly_large_and_useful_query
, and the result set it produces I want to use as part of several future queries.
如何避免在多个位置复制和粘贴相同的查询?我如何不重复自己"并遵循DRY原则.在SQL中可以吗?
How can I avoid copying and pasting the same queries in multiple places? How can I "not repeat myself," and follow DRY principles. Is this possible in SQL?
我正在使用Oracle SQL.首选便携式SQL解决方案,但如果我必须使用Oracle特定功能(包括PL/SQL),那就可以了.
I'm using Oracle SQL. Portable SQL solutions are preferable, but if I have to use an Oracle specific feature (including PL/SQL) that's OK.
推荐答案
create view horrible_query_1_VIEW as
select .. ...
from .. .. ..
create view ugly_query_2_VIEW as
select .. ...
from .. .. ..
然后
(horrible_query_1_VIEW) minus (ugly_query_2_VIEW)
(ugly_query_2_VIEW) minus (horrible_query_1_VIEW)
或者,也许使用with clause
:
with horrible_query_1 as (
select .. .. ..
from .. .. ..
) ,
ugly_query_2 as (
select .. .. ..
.. .. ..
)
(select * from horrible_query_1 minus select * from ugly_query_2 ) union all
(select * from ugly_query_2 minus select * from horrible_query_1)
这篇关于如何在不重复的情况下重用大型查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!