代码之家 › 专栏 › 技术社区 › Braham Shakti

获取oracle表中具有不同多列的唯一行

oracle12c oracle11g oracle sql

Braham Shakti · 技术社区 · 7 年前

id   loc_id   org_id   comp1   comp2   comp3   comp4  paper_id
1      5        4       A        C       B     (null)    1
2      6        3       F        D       E     (null)    1
3      5        4       C        B       A     (null)    1
4      6        3       E        F       D     (null)    1

id   loc_id   org_id   comp1   comp2   comp3   comp4  paper_id
3      5        4       C        B       A     (null)    1
4      6        3       E        F       D     (null)    1

注意,我想要一个唯一的行,在列名中包含max id和不同的comp1、comp2、comp3、comp4值,但是值应该相同。

e、 g如果第一行分别在组件1、组件2和组件3中有A、B、C 第二行分别在comp1、comp2和comp3中有C、B、A 我要一排。

我尝试了下面的查询,但这没有返回唯一的行

SELECT DISTINCT loc_id, org_id, comp1, comp2, comp3, comp4, MAX(id)
  FROM tags t
  WHERE t.paper_id = 1
  GROUP BY loc_id, org_id, comp1, comp2, comp3, comp4;

4 回复 | 直到 7 年前

Marmite Bomber 7 年前

在第一步中连接 compN

你会用 LISTAGG -注意,您使用 分隔符 和 NVL 函数使结果唯一-您要将(A,A,null,null)解释为(AA,null,null,null)的副本

with t1 as (
select id, nvl(comp1,'-') comp from test union all
select id, nvl(comp2,'-') comp from test union all
select id, nvl(comp3,'-') comp from test union all
select id, nvl(comp4,'-') comp from test)
select id,
listagg(comp,',') within group (order by comp) as comp_key
from t1
group by id
order by 2
;


       ID COMP_KEY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
---------- --------
         3 -,A,B,C
         1 -,A,B,C
         4 -,D,E,F
         2 -,D,E,F

剩下的就简单到 COMP_KEY

下面是一个选择最高 ID 每组(见 max(id) 在里面 t3 子查询)。

with t1 as (
select id, nvl(comp1,'-') comp from test union all
select id, nvl(comp2,'-') comp from test union all
select id, nvl(comp3,'-') comp from test union all
select id, nvl(comp4,'-') comp from test),
t2 as (
select id,
listagg(comp,',') within group (order by comp) as comp_key
from t1
group by id
), t3 as (
select comp_key, max(id) id from t2
group  by comp_key)
select * from test 
where id in (select id from t3)
order by id
;

        ID     LOC_ID     ORG_ID COMP1 COMP2 COMP3 COMP4        PAPER_ID
---------- ---------- ---------- ----- ----- ----- ---------- ----------
         3          5          4 C     B     A                         1 
         4          6          3 E     F     D                         1

Littlefoot 7 年前

这里有一个选择,看看是否适合你的需要。

为了做到这一点,我将创建一个函数,对4个参数进行排序(表示 comp1, ..., comp4 从你的样本数据)。

SQL> create or replace function f_sort(p1 in varchar2, p2 in varchar2,
  2                                    p3 in varchar2, p4 in varchar2)
  3  return varchar2
  4  is
  5    /* sorts IN parameters. For example: if IN parameters are a, d, c, b
  6       then RETVAL = a:b:c:d
  7    */
  8    retval varchar2(200);
  9  begin
 10    with temp (p) as
 11      (select p1 from dual union all select p2 from dual union all
 12       select p3 from dual union all select p4 from dual
 13      )
 14    select listagg(p, ':') within group (order by p)
 15      into retval
 16    from temp;
 17
 18    return retval;
 19  end;
 20  /

Function created.

SQL>

我需要它做什么?选择 MAX(ID) 对于 比较1,…,比较4 价值观-正如你所描述的(在 inter

SQL> with test (id, loc_id, org_id, comp1, comp2, comp3, comp4) as
  2  (select 1, 5, 4, 'a', 'c', 'b', null from dual union all
  3   select 2, 6, 3, 'f', 'd', 'e', null from dual union all
  4   select 3, 5, 4, 'c', 'b', 'a', null from dual union all
  5   select 4, 6, 3, 'e', 'f', 'd', null from dual
  6  ),
  7  inter as
  8  (select max(id) maxid, f_sort(comp1, comp2, comp3, comp4) srt
  9   from test
 10   group by f_sort(comp1, comp2, comp3, comp4)
 11  )
 12  select t.*
 13  from test t join inter i on i.maxid = t.id
 14  order by t.id;

        ID     LOC_ID     ORG_ID COMP1 COMP2 COMP3 COMP4
---------- ---------- ---------- ----- ----- ----- -----
         3          5          4 c     b     a
         4          6          3 e     f     d

SQL>

Braham Shakti 7 年前

多亏了 @marmite-bomber

SELECT comp_key, loc_id, org_id, max_id, 1, sysdate, sysdate
FROM (
  SELECT comp_key, max(id) max_id
  FROM (
    WITH t1 AS (
      SELECT t.id, t.loc_id, t.org_id, nvl(comp1,0) comp FROM tags t WHERE t.paper_id = 1 AND t.paper_id IS NOT NULL UNION 
      SELECT t.id, t.loc_id, t.org_id, nvl(comp2,0) comp FROM tags t WHERE t.paper_id = 1 AND t.paper_id IS NOT NULL UNION
      SELECT t.id, t.loc_id, t.org_id, nvl(comp3,0) comp FROM tags t WHERE t.paper_id = 1 AND t.paper_id IS NOT NULL UNION
      SELECT t.id, t.loc_id, t.org_id, nvl(comp1,0) comp FROM tags t WHERE t.paper_id = 1 AND t.paper_id IS NOT NULL
    )
    SELECT t1.id, t1.loc_id loc_id, t1.org_id org_id, listagg(comp,',') within group (ORDER BY comp) AS comp_key
    FROM t1
    GROUP BY t1.id, t1.loc_id, t1.org_id
  )
  GROUP BY comp_key, loc_id, org_id
) t2, tags tg
WHERE tg.id = t2.max_id;

Barbaros Özhan 7 年前

你可以通过分组得到你想要的结果 greatest , least 功能,及其 mean (平均值)通过将相关列转换为整数的ascii函数的贡献:

select * 
  from tags
 where id in 
  (
  select max(id)
    from tags
   group by least(ascii(comp1),ascii(comp2),ascii(comp3)),
         greatest(ascii(comp1),ascii(comp2),ascii(comp3)),
         (least(ascii(comp1),ascii(comp2),ascii(comp3))+
          greatest(ascii(comp1),ascii(comp2),ascii(comp3)))/2
  );

SQL Fiddle Demo