如何通过对多列进行分组来创建视图？

Question

也许这个问题有点模糊。我想创建一个视图或使用一个查询来更改这一点-->

012878     123456 Mike                             12345678 Saving                  1234.52
012878     123456 Mike                             22345678 credit                 -1534.52
012878     123456 Mike                             32345678 Home loan            -453234.52
012878     123458 Jean                             11001234 SAVING                  3213.54
012878     123458 Jean                             21001234 Credit                  -120.34
012878     123458 Jean                             31001234 Personal loan         -63121.23

到这个->

    012878     123456 Mike          12345678 Saving                  1234.52
                                    22345678 credit                 -1534.52
                                    32345678 Home loan            -453234.52
    012878     123458 Jean          11001234 SAVING                  3213.54
                                    21001234 Credit                  -120.34
                                    31001234 Personal loan         -63121.23

我有两张表：

SQL> CREATE TABLE Account (
  2          BSB#            CHAR(6)         NOT NULL, /* Bank BSB number    */
  3          Customer#       NUMBER(10)      NOT NULL, /* Customer number    */
  4          Account#        NUMBER(10)      NOT NULL, /* Account number     */
  5          Type            VARCHAR2(20)    NOT NULL, /* Account type       */
  6          Balance         NUMBER(10,2)    NOT NULL, /* Account balance    */
  7          CONSTRAINT WorksOn_PK PRIMARY KEY(BSB#, Account#),
  8          CONSTRAINT WorksOn_FK1 FOREIGN KEY(BSB#, Customer#) REFERENCES Customer(BSB#, Customer#)
  9  )


SQL> CREATE TABLE Customer (
  2          BSB#            CHAR(6)         NOT NULL, /* Bank BSB number    */
  3          Customer#       NUMBER(10)      NOT NULL, /* Customer number    */
  4          Name            VARCHAR2(30)    NOT NULL, /* Customer name      */
  5          DOB             Date,                     /* Date of birth      */
  6          Sex             CHAR,                     /* M-Male, F-Female   */
  7          Address         VARCHAR2(50)    NOT NULL, /* Customer address   */
  8          Phone#          VARCHAR2(15),             /* Phone number       */
  9          CONSTRAINT Project_PK PRIMARY KEY(BSB#, Customer#),
 10          CONSTRAINT Project_FK FOREIGN KEY (BSB#) REFERENCES Bank(BSB#)
 11  );

为了生成第一个视图，我使用了这个-->

create view view_1 AS
select
a.bsb#, a.customer#, c.name, a.account#, a.type, a.balance
from
account a, customer c
where
c.customer# = a.customer# and c.bsb# = a.bsb#

现在我想按bsb#、account#和name对其进行分组。换句话说，我希望消除行，并将它们更改为空格或空值。

Answer 1

如果您在报告中需要此功能，请尝试搜索组功能，并将一些字段放在组标题中，将其他列放在组详细信息中。

Answer 2

您可以使用此示例作为模板，并将您自己的列/查询作为数据源，而不是表T：

SQL> With t (a, b, c, d, e, f) as (
  2  select '012878',123456,'Mike',12345678,'Saving', 1234.52 from dual union all
  3  select '012878',123456,'Mike',22345678,'credit',-1534.52 from dual union all
  4  select '012878',123456,'Mike',32345678,'Home loan', -453234.52 from dual union all
  5  select '012878',123458,'Jean',11001234,'SAVING', 3213.54 from dual union all
  6  select '012878',123458,'Jean',21001234,'Credit', -120.34 from dual union all
  7  select '012878',123458,'Jean',31001234,'Personal loan', -63121.23 from dual
  8  )
  9  select decode(rn,1,a,null) a, decode(rn,1,b,null) b, decode(rn,1,c,null) c,
 10  d,e,f
 11  from (
 12  select t.*, row_number() over(partition by a,b,c order by d) rn
 13  from t
 14  ) x
 15  order by x.a, x.b, x.c, x.d
 16  /

A               B C             D E                      F                      
------ ---------- ---- ---------- ------------- ----------                      
012878     123456 Mike   12345678 Saving           1234,52                      
                         22345678 credit          -1534,52                      
                         32345678 Home loan     -453234,52                      
012878     123458 Jean   11001234 SAVING           3213,54                      
                         21001234 Credit           -120,34                      
                         31001234 Personal loan  -63121,23    

Answer 3

我最终通过sql plus中的格式化工具解决了这个问题。正如我在问题中所说的，创建一个视图或查询，因为我需要在sqlplus命令行中进行报告：

create view view_4(BSB#, CUSTOMER#, NAME, ACCOUNT, TYPE, BALANCE) AS (
select
 a.bsb#, a.customer#, c.name, a.account#, a.type, a.balance
from
account a
RIGHT OUTER JOIN customer c
ON
c.customer# = a.customer# and c.bsb# = a.bsb#)


BREAK ON BSB# ON CUSTOMER# ON NAME


SELECT BSB#, CUSTOMER#, NAME, TYPE
    FROM VIEW_3
    ORDER BY CUSTOMER#;​

现在的结果正是我想要的.