时间戳

首页 > 解决方案 > 根据两行的数据创建一行

sql - 根据两行的数据创建一行

问题描述

我已经查看了以前类似的问题，但我找不到这个确切的场景。我不是想将两行中的数据连接成一列。我正在尝试根据两行的数据构建一行。我不确定这是否可以通过内置的 oracle 函数完成，或者它需要是自定义函数。以下表为例。

create table reporttable( Location varchar2(10), Manager varchar2(10), ManagerType varchar2(1), User_Id Number, Region varchar2(10)); 

insert into reporttable select USA, 'Robert', 'A', '123', 'East'  from dual;
insert into reporttable select USA, 'Unknown', 'A', '123', 'Unknown'  from dual;
commit;

  Location  Manager     ManagerType  User_Id Region 
        USA Robert             A      123    Unknown
        USA Unknown            A      123    East

我想要的输出是因为ManagerType和User_Id表明它是同一个 Manager

Location  Manager     ManagerType  User_Id Region 
      USA Robert             A      123    East

标签： sqloracleselect

解决方案

假设您要更新所有重复项以仅保留一行并删除其他行，并且保留的managerandregion值应该是最大的非未知值，那么您可以使用：

MERGE INTO reporttable dst
USING (
  SELECT rowid AS rid,
         COALESCE(
           MAX( DECODE( manager, 'Unknown', NULL, manager ) ) OVER (
             PARTITION BY location, user_id, managertype
           ),
           'Unknown'
         ) AS manager,
         COALESCE(
           MAX( DECODE( region, 'Unknown', NULL, region ) ) OVER (
             PARTITION BY location, user_id, managertype
           ),
           'Unknown'
         ) AS region,
         ROW_NUMBER() OVER (
           PARTITION BY location, user_id, managertype
           ORDER BY ROWNUM
         ) AS rn,
         COUNT(*) OVER (
           PARTITION BY location, user_id, managertype
         ) AS cnt
  FROM   reporttable
) src
ON ( src.rid = dst.ROWID AND src.cnt > 1 )
WHEN MATCHED THEN
  UPDATE
  SET manager = src.manager,
      region  = src.region
  DELETE WHERE src.rn > 1;

其中，对于您的示例数据：

create table reporttable(
  Location varchar2(10),
  Manager varchar2(10),
  ManagerType varchar2(1),
  User_Id Number,
  Region varchar2(10)
);
insert into reporttable
select 'USA', 'Robert', 'A', '123', 'East'  from dual union all
select 'USA', 'Unknown', 'A', '123', 'Unknown'  from dual;

然后在MERGEthen 之后：

SELECT * FROM reporttable;

输出：

地点 经理 经理类型 用户身份 地区
美国 罗伯特 一个 123 东方

如果您只想SELECT从表中提取，那么您可以使用相同的方法并ROW_NUMBER在每个分区内进行过滤：

SELECT location,
       manager,
       managertype,
       user_id,
       region
FROM   (
  SELECT location,
         COALESCE(
           MAX( DECODE( manager, 'Unknown', NULL, manager ) ) OVER (
             PARTITION BY location, user_id, managertype
           ),
           'Unknown'
         ) AS manager,
         managertype,
         user_id,
         COALESCE(
           MAX( DECODE( region, 'Unknown', NULL, region ) ) OVER (
             PARTITION BY location, user_id, managertype
           ),
           'Unknown'
         ) AS region,
         ROW_NUMBER() OVER (
           PARTITION BY location, user_id, managertype
           ORDER BY ROWNUM
         ) AS rn
  FROM   reporttable
) 
WHERE rn = 1

db<>在这里摆弄

推荐阅读