如何创建具有多个选择查询的视图，该视图在 oracle 中返回多行? [英] How to create a view with multiple select queries that returns multiple rows in oracle?

问题描述

我在 Oracle 中有一个这样的表表名:链接

I have a table like this in Oracle table name: links

|url             |category|
|----------------|--------|
|www.google.com  |search  |
|----------------|--------|
|www.facebook.com|Social  |
|----------------|--------|
|www.twiter.com  |Social  |
|----------------|--------|

我必须创建一个这样的视图

and I have to create a view like this

|search          |Social          |
|----------------|----------------|
|www.google.com  |www.facebook.com|
|----------------|----------------|
|                |www.twiter.com  |
|----------------|----------------|

我试过下面的sql

CREATE OR REPLACE VIEW v1 AS(
    SELECT(
        (SELECT * FROM Links WHERE category='Search'),
        (SELECT * FROM Links WHERE category='Social')
    ) FROM Dual);

但它返回以下错误:

缺少右括号

当我尝试运行 sql 的以下部分时

when I try to run the following section of the sql

SELECT(
        (SELECT * FROM Links WHERE category='Search'),
        (SELECT * FROM Links WHERE category='Social')
    ) FROM Dual;

它返回以下错误:

Signle 子查询返回多行.

Signle subquery returns multiple rows.

请有人帮助我.如果有可能，也请告诉我.谢谢

Please somebody help me. And also let me know if it is possible at all. Thanks

推荐答案

您可以使用包含解析函数 ROW_NUMBER() 的 PIVOT 子句:

You can use PIVOT clause including analytic function ROW_NUMBER() :

SELECT "search", "Social"
  FROM (
        SELECT t.*,
               ROW_NUMBER() OVER (PARTITION BY category ORDER BY url) AS rn
          FROM t
        )
 PIVOT
    (
     MAX(url) FOR category IN ( 'search' AS "search", 'Social' AS "Social" )
    )
 ORDER BY rn

并且如果 SELECT 语句由这部分 CREATE OR REPLACE VIEW v1 AS 前置，那么您将拥有一个名为 v1<的新数据库视图/code> 返回该语句的内容

and if the SELECT statement is prepended by this part CREATE OR REPLACE VIEW v1 AS, then you'll have a new database view with name v1 returning the content of that statement by

SELECT * FROM v1

演示

更新:您仍然可以选择使数据透视动态与即将到来的与当前类别值不同的类别值.创建一个包含 REFCURSOR 的函数(或过程)，例如 ;

Update : You have still an option to make the pivot dynamic versus upcoming different category values than the current ones. Create a function(or procedure) including REFCURSOR such as ;

CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  SELECT LISTAGG( ''''||category||''' AS "'||LOWER(category)||'"' , ',' )
                 WITHIN GROUP ( ORDER BY category )
    INTO v_str
    FROM (
          SELECT DISTINCT category
            FROM t
          );

  v_sql :=
  'SELECT *
     FROM (
           SELECT t.*,
                  ROW_NUMBER() OVER (PARTITION BY category ORDER BY url) AS rn
             FROM t
          )
    PIVOT
    (
     MAX(url) FOR category IN ( '|| v_str ||' )
    )
    ORDER BY rn';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

然后运行这段代码:

VAR rc REFCURSOR
EXEC :rc := Get_Categories_RS;
PRINT rc

从 SQL Developer 的命令行查看结果集.

from SQL Developer's Command Line in order to see the result set.

这篇关于如何创建具有多个选择查询的视图，该视图在 oracle 中返回多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！