Oracle 将多行合并为一行

发布于 2024-08-30 08:13:29 字数 1698 浏览 5 评论 0原文

我正在使用一个在 Oracle 中创建并通过 SDE 在 GIS 软件中使用的数据库。我的一位同事要从此数据库中进行一些统计，但我无法找到合理的 SQL 查询来获取数据。

我有两张桌子，一张有注册信息，一张有注册详细信息。这是一种一对多的关系，因此注册可以有一个或多个与之相关的详细信息（没有最大数量）。

table: Registration

RegistrationID          Date       TotLenght
1                    01.01.2010        5
2                    01.02.2010        15
3                    05.02.2009        10

2.table: RegistrationDetail

DetailID     RegistrationID   Owner      Type      Distance
1                  1           TD          UB          1,5
2                  1           AB          US          2
3                  1           TD          UQ          4
4                  2           AB          UQ         13
5                  2           AB          UR         13,1
6                  3           TD          US          5

我希望结果选择是这样的：

RegistrationID          Date       TotLenght DetailID     RegistrationID   Owner     Type      Distance  DetailID     RegistrationID   Owner      Type      Distance  DetailID     RegistrationID   Owner      Type      Distance
1                    01.01.2010        5         1              1           TD        UB          1,5          2               1          AB        US          2         3                  1              TD          UQ          4
2                    01.02.2010        15        4              2           AB        UQ         13            5               2          AB        UR         13,1
3                    05.02.2009        10        6              3           TD        US          5

使用正常的连接，每个注册和详细信息都会得到一行。谁能帮我解决这个问题吗？我没有数据库的管理员权限，因此无法创建任何表或变量。如果可能的话，我可以将表复制到 Access 中。

原文

I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data.

I have two tables, one with registrations and one with registrationdetails. It's a one to many relationship, so the registration can have one or more details connected to it (no maximum number).

table: Registration

RegistrationID          Date       TotLenght
1                    01.01.2010        5
2                    01.02.2010        15
3                    05.02.2009        10

2.table: RegistrationDetail

DetailID     RegistrationID   Owner      Type      Distance
1                  1           TD          UB          1,5
2                  1           AB          US          2
3                  1           TD          UQ          4
4                  2           AB          UQ         13
5                  2           AB          UR         13,1
6                  3           TD          US          5

I want the resulting selection to be something like this:

RegistrationID          Date       TotLenght DetailID     RegistrationID   Owner     Type      Distance  DetailID     RegistrationID   Owner      Type      Distance  DetailID     RegistrationID   Owner      Type      Distance
1                    01.01.2010        5         1              1           TD        UB          1,5          2               1          AB        US          2         3                  1              TD          UQ          4
2                    01.02.2010        15        4              2           AB        UQ         13            5               2          AB        UR         13,1
3                    05.02.2009        10        6              3           TD        US          5

With a normal join I get one row per each registration and detail. Can anyone help me with this? I don't have administrator-rights for the database, so I can't create any tables or variables. If it's possible, I could copy the tables into Access.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

面如桃花 2024-09-06 08:13:29

如果详细记录的最大数量是固定的并且已知，则可以完成此操作。数字越大，查询编码就越繁琐。这就是大自然给我们剪切和粘贴的原因。

以下查询使用了一些技巧。公共表表达式（又名子查询分解）子句封装了 RegistrationDetail 上的查询，因此我们可以轻松地在多个位置引用它。子查询使用分析函数 ROW_NUMBER()，它允许我们识别 RegistrationID 组中的每个详细记录。这两个功能都是在 Oracle 9i 中引入的，因此它们并不是新功能，但很多人仍然不了解它们。

主查询使用外连接将注册表多次连接到子查询中的行。它连接 RegistrationID 和派生的 DetNo。

SQL> with dets as
  2      ( select
  3              registrationid
  4              , owner
  5              , type
  6              , distance
  7              , detailid
  8              , row_number() over (partition by registrationid
  9                                   order by detailid) as detno
 10          from registrationdetail )
 11  select
 12      reg.registrationid
 13      , reg.somedate
 14      , reg.totlength
 15      , det1.detailid as detId1
 16      , det1.owner as owner1
 17      , det1.type as type1
 18      , det1.distance as distance1
 19      , det2.detailid as detId2
 20      , det2.owner as owner2
 21      , det2.type as type2
 22      , det2.distance as distance2
 23      , det3.detailid as detId3
 24      , det3.owner as owner3
 25      , det3.type as type3
 26      , det3.distance as distance3
 27  from registration reg
 28          left join dets det1 on ( reg.registrationid = det1.registrationid
 29                                   and det1.detno = 1 )
 30          left join dets det2 on ( reg.registrationid = det2.registrationid
 31                                   and det2.detno = 2 )
 32          left join dets det3 on ( reg.registrationid = det3.registrationid
 33                                   and det3.detno = 3 )
 34  order by reg.registrationid
 35  /
REGISTRATIONID SOMEDATE   TOTLENGTH     DETID1 OW TY  DISTANCE1     DETID2 OW TY  DISTANCE2     DETID3 OW TY  DISTANCE3
-------------- --------- ---------- ---------- -- -- ---------- ---------- -- -- ---------- ---------- -- -- ----------
             1 01-JAN-10          5          1 TD UB        1.5          2 AB US          2          3 TD UQ          4
             2 01-FEB-10         15          4 AB UQ         13          5 AB UR       13.1
             3 05-FEB-09         10          6 TD US          5

SQL>

显然，如果每个 RegistrationID 有四个详细记录，您将需要其中四个外部联接（以及投影中的四组列）。

编辑

我刚刚重新阅读了您的问题，发现了可怕的词语“没有最大数量”。抱歉，那样的话你就不走运了。使用可变数量的集合解决此问题的唯一方法是使用动态 SQL，您已有效地排除了这种方法（因为您需要创建额外的架构对象）。

编辑2

还有另一种解决方案，它只是提取数据并忘记布局。 Oracle 允许我们在投影中与标量一起声明内联游标，即嵌套的 select 语句。这解决了向客户端工具显示输出的问题。

在此版本中，我使用 Oracle 的内置 XML 功能来生成输出（基于目前许多工具都可以呈现 XML）。 RegistrationDetails 记录分组在名为 REG_DETAILS 的 XMLElement 中，该元素嵌套在每个注册记录中。

with dets as
    ( select
            registrationid
            , owner
            , type
            , distance
            , detailid
            , row_number() over (partition by registrationid
                                 order by detailid) as detno
        from registrationdetail )
select
    xmlelement("AllRegistrations"
        , xmlagg(
            xmlelement("Registration"
               , xmlforest( reg.registrationid
                            , reg.somedate
                            , reg.totlength
                            , ( select xmlagg(
                                        xmlelement("RegDetail"
                                            , xmlforest(dets.detailid
                                                        , dets.owner
                                                        , dets.type
                                                        , dets.distance
                                                        , dets.detno
                                                        )
                                                    )
                                                )
                                from dets 
                                where reg.registrationid = dets.registrationid
                              ) as "RegDetails"
                          )
                      )
                 )
              )
from registration reg
order by reg.registrationid
/

If the maximum number of Detail records is fixed and known then this can be done. The larger the number the more tedious the query is to code. That's why Nature gave us cut'n'paste.

The following query uses a couple of tricks. The Common Table Expression (aka Sub-Query Factoring) clause encapsulates the query on RegistrationDetail so we can easily refer to it in multiple places. The sub-query uses an Analytic function ROW_NUMBER() which allows us to identify each Detail record within the RegistrationID group. Both these features wwre introduced in Oracle 9i so they aren't new, but lots of people still don't know about them.

The main query uses Outer Joins to connect the Registration table multiple times to rows in the sub-query. It joins on RegistrationID and the derived DetNo.

SQL> with dets as
  2      ( select
  3              registrationid
  4              , owner
  5              , type
  6              , distance
  7              , detailid
  8              , row_number() over (partition by registrationid
  9                                   order by detailid) as detno
 10          from registrationdetail )
 11  select
 12      reg.registrationid
 13      , reg.somedate
 14      , reg.totlength
 15      , det1.detailid as detId1
 16      , det1.owner as owner1
 17      , det1.type as type1
 18      , det1.distance as distance1
 19      , det2.detailid as detId2
 20      , det2.owner as owner2
 21      , det2.type as type2
 22      , det2.distance as distance2
 23      , det3.detailid as detId3
 24      , det3.owner as owner3
 25      , det3.type as type3
 26      , det3.distance as distance3
 27  from registration reg
 28          left join dets det1 on ( reg.registrationid = det1.registrationid
 29                                   and det1.detno = 1 )
 30          left join dets det2 on ( reg.registrationid = det2.registrationid
 31                                   and det2.detno = 2 )
 32          left join dets det3 on ( reg.registrationid = det3.registrationid
 33                                   and det3.detno = 3 )
 34  order by reg.registrationid
 35  /
REGISTRATIONID SOMEDATE   TOTLENGTH     DETID1 OW TY  DISTANCE1     DETID2 OW TY  DISTANCE2     DETID3 OW TY  DISTANCE3
-------------- --------- ---------- ---------- -- -- ---------- ---------- -- -- ---------- ---------- -- -- ----------
             1 01-JAN-10          5          1 TD UB        1.5          2 AB US          2          3 TD UQ          4
             2 01-FEB-10         15          4 AB UQ         13          5 AB UR       13.1
             3 05-FEB-09         10          6 TD US          5

SQL>

Obviously if you have four Detail records per RegistrationID you will need four of those Outer Joins (and four sets of columns in the projection).

edit

I have just re-read your question and spotted the dread words "No maximum number". Sorry, in that case you're out of luck. The only way of solving this problem with a variable number of sets is with dynamic SQL, which you have effectively ruled out (because you would need to create additional schema objects).

edit 2

There is another solution, which is just about extracting the data and forgetting the layout. Oracle allows us to declare inline cursors, that is nested select statements, in the projection alongside scalars. This passes the problem of displaying the output to a client tool.

In this version I use Oracle's built-in XML functionality to produce the output (on the basis that lots of tools can render XML these days). The RegistrationDetails records are group within an XMLElement called REG_DETAILS which is nested within each Registration record.

with dets as
    ( select
            registrationid
            , owner
            , type
            , distance
            , detailid
            , row_number() over (partition by registrationid
                                 order by detailid) as detno
        from registrationdetail )
select
    xmlelement("AllRegistrations"
        , xmlagg(
            xmlelement("Registration"
               , xmlforest( reg.registrationid
                            , reg.somedate
                            , reg.totlength
                            , ( select xmlagg(
                                        xmlelement("RegDetail"
                                            , xmlforest(dets.detailid
                                                        , dets.owner
                                                        , dets.type
                                                        , dets.distance
                                                        , dets.detno
                                                        )
                                                    )
                                                )
                                from dets 
                                where reg.registrationid = dets.registrationid
                              ) as "RegDetails"
                          )
                      )
                 )
              )
from registration reg
order by reg.registrationid
/

回复收藏 0 原文

巷雨优美回忆 2024-09-06 08:13:29

在同一个查询中不能有多个具有相同名称的列 - Oracle 会将它们重命名为“Date_1”、“Date_2”等。拥有多行有什么问题？您如何访问它？

回复收藏 0 原文

伴随着你 2024-09-06 08:13:29

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,
    ('Created By:'||wsl.workstep_sign||'  On') As Description,
    wsl.workstep_date,
     listagg('Action Performed By,' ||woa.mutator||'At date' ||       



    SELECT   
    wo_h.event_perfno_i AS WO,
    wo_h.ata_chapter,
        wo_h.created_by AS WorkOrderCreator,
    	wo_h.issue_date As Work_Order_IssueDate, 
    	wo_h.ac_registr As WorkOrderACRegister,
    	wo_h.state As WorkOrderState,

    ('Created By:'||wsl.workstep_sign||'  On') As Description,

    wsl.workstep_date,




    listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
       FROM workstep_link wsl
     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

    WHERE
    wo_h.state = 'O'
 

          AND wo_h.event_perfno_i = '5690136'
         AND wo_h.ac_registr = 'AEC'
        GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
        wsl.workstep_time,
        wsl.workstep_sign
    
    
    woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)

       FROM workstep_link wsl

     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i

      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i
    WHERE

    wo_h.state = 'O'

       AND wo_h.event_perfno_i = '5690136'

     AND wo_h.ac_registr = 'AEC'

    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,

    wsl.workstep_time,

    wsl.workstep_sign

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,
    ('Created By:'||wsl.workstep_sign||'  On') As Description,
    wsl.workstep_date,
     listagg('Action Performed By,' ||woa.mutator||'At date' ||       



    SELECT   
    wo_h.event_perfno_i AS WO,
    wo_h.ata_chapter,
        wo_h.created_by AS WorkOrderCreator,
    	wo_h.issue_date As Work_Order_IssueDate, 
    	wo_h.ac_registr As WorkOrderACRegister,
    	wo_h.state As WorkOrderState,

    ('Created By:'||wsl.workstep_sign||'  On') As Description,

    wsl.workstep_date,




    listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
       FROM workstep_link wsl
     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

    WHERE
    wo_h.state = 'O'
 

          AND wo_h.event_perfno_i = '5690136'
         AND wo_h.ac_registr = 'AEC'
        GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
        wsl.workstep_time,
        wsl.workstep_sign
    
    
    woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)

       FROM workstep_link wsl

     join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i

      join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i
    WHERE

    wo_h.state = 'O'

       AND wo_h.event_perfno_i = '5690136'

     AND wo_h.ac_registr = 'AEC'

    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,

    wsl.workstep_time,

    wsl.workstep_sign

回复收藏 0 原文

蓝天 2024-09-06 08:13:29

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,

('Created By:'||wsl.workstep_sign||'  On') As Description,

wsl.workstep_date,




listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
   FROM workstep_link wsl
 join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
  join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

WHERE
wo_h.state = 'O'


      AND wo_h.event_perfno_i = '5690136'
     AND wo_h.ac_registr = 'AEC'
    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
    wsl.workstep_time,
    wsl.workstep_sign

SELECT   
wo_h.event_perfno_i AS WO,
wo_h.ata_chapter,
    wo_h.created_by AS WorkOrderCreator,
    wo_h.issue_date As Work_Order_IssueDate, 
    wo_h.ac_registr As WorkOrderACRegister,
    wo_h.state As WorkOrderState,

('Created By:'||wsl.workstep_sign||'  On') As Description,

wsl.workstep_date,




listagg('Action Performed By,' ||woa.mutator||'At date' || woa.action_date|| '.'|| woa.text,'.. ')   WITHIN GROUP ( ORDER BY woa.text)   
   FROM workstep_link wsl
 join wo_header wo_h on wsl.event_perfno_i=wo_h.event_perfno_i
  join  wo_text_action woa on wsl.workstep_linkno_i=woa.workstep_linkno_i

WHERE
wo_h.state = 'O'


      AND wo_h.event_perfno_i = '5690136'
     AND wo_h.ac_registr = 'AEC'
    GROUP BY wo_h.event_perfno_i, wo_h.ata_chapter, wo_h.created_by,wo_h.issue_date,wo_h.ac_registr,wo_h.state,wsl.workstep_date,
    wsl.workstep_time,
    wsl.workstep_sign

回复收藏 0 原文

~没有更多了~