在 Oracle 中为多个查询创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13176879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
create a view for multiple queries in Oracle
提问by paranza
I have 4 SQL queries that fetch data from the same table and I was wondering whether you can create a view containing multiple queries. For instance, one of these queries looks something like:
我有 4 个从同一个表中获取数据的 SQL 查询，我想知道您是否可以创建一个包含多个查询的视图。例如，这些查询之一类似于：
SELECT Count(COLUMN1) FROM TABLE1 WHERE COLUMN3 > '01-Oct-12' AND COLUMN3< '01-Nov-12'
whereas another looks like:
而另一个看起来像：
SELECT Count(COLUMN2) FROM TABLE1 WHERE COLUMN3 > '01-Oct-12' AND COLUMN3 < '01-Nov-12'
Thanks,
谢谢，
I.
一世。
回答by jainvikram444
CREATE VIEW viewname AS
SELECT Count(COLUMN1) as col1 FROM TABLE1 WHERE COLUMN3 > '01-Oct-12' AND COLUMN3< '01-Nov-12'
union all
SELECT Count(COLUMN2) as col1 FROM TABLE1 WHERE COLUMN3 > '01-Oct-12' AND COLUMN3 < '01-Nov-12'
回答by Taryn
If you want the data in the same recordset and same column, then you can use UNION ALL:
如果您希望数据位于同一记录集和同一列中，则可以使用UNION ALL：
SELECT Count(COLUMN1) As CNT, 'Q1' as Src
FROM TABLE1
WHERE COLUMN3 > '01-Oct-12'
AND COLUMN3< '01-Nov-12'
UNION ALL
SELECT Count(COLUMN2) as Cnt, 'Q2' as Src
FROM TABLE1
WHERE COLUMN3 > '01-Oct-12'
AND COLUMN3 < '01-Nov-12'
I included a column, to help you identify which query your data is coming from. If that is unneeded then you can drop using it.
我包含了一个列，以帮助您确定数据来自哪个查询。如果不需要，那么您可以放弃使用它。
回答by APC
The simplest implementation would be:
最简单的实现是：
create or replace view v23 as
select count(column1) as col1_count
, count(column2) as col2_count
from TABLE1
WHERE COLUMN3 > '01-Oct-12' AND COLUMN3 < '01-Nov-12'
But if not all your queries use the same criteria you could use scalar sub-queries:
但如果不是所有查询都使用相同的条件，则可以使用标量子查询：
create or replace view v23 as
select
(select count(column1) from TABLE1
WHERE COLUMN3 > '01-Oct-12' AND COLUMN3 < '01-Nov-12' ) as col1_count
, (select count(column2) from TABLE2
WHERE COLUMN3 > '01-Oct-12' AND COLUMN3 < '01-Nov-12' ) as col2_count
from dual
