内部联接两个表，聚合varchar字段 [英] Inner Join Two Table, aggregating varchar fields

问题描述

对不起，我的问题有所改变. 我有两个表，我想合并它们

Sorry I change my question a bit . I have two table and I want to merge them

  TERMS_TABLE

 ID  | TERMS
 309 | 'hardware'
 309 | 'software'

TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....'

现在我想像这样将TERMS_TABLE的TERMS列添加到TFIDF_TABLE的条件列

Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE like that

结果应为:

NEW_TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....,hardware,software'

我尝试以下代码:

Insert into    NEW_TFIDF_TABLE
SELECT T.ID, T.TERMS ||', '|| TT.TERMS FROM TFIDF_TABLE T
INNER JOIN TERMS_TABLE TT  ON  T.ID=TT.ID

但是它不能满足我的要求 我怎样才能做到这一点 ?

but it does not do what I want How can I do that ?

修改:我忘记了一个问题.如果TFIDF_TABLE已经包含TERMS_TABLE的TERMS 那么我不应该将此术语插入NEW_TFIDF_TABLE.要执行此控制机制，我该怎么办?

Modification: I forgot an issue. If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE. To do this control mechanism what should I do ?

示例:

TERMS_TABLE

TERMS_TABLE

 ID  | TERMS
 309 | 'hardware'
 309 | 'software'
 309 | 'computer'



TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....'

结果:

NEW_TFIDF_TABLE

NEW_TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....,hardware,software'

推荐答案

如果您拥有Oracle 11g，则可以使用LISTAGG函数，如下所示:

If you got Oracle 11g, you can use the LISTAGG function, like so:

Insert into    NEW_TFIDF_TABLE
SELECT T.ID, T.TERMS ||', '|| LISTAGG(TT.TERMS, ',') 
FROM TFIDF_TABLE T
INNER JOIN TERMS_TABLE TT  ON  T.ID=TT.ID
GROUP BY t.ID, t.TERMS /* assuming the id is unique in t */

如果使用的是较早版本，则可以使用包创建自己的聚合函数.我已经构建了这个FNC_CONCATCOMMASEPARATED函数，我经常使用它自己.

If you're on an earlier version, you can create your own aggregate functions using a package. I've build this FNC_CONCATCOMMASEPARATED function, which I freqently use myself.

CREATE OR REPLACE TYPE "AT_CONCATCOMMASEPARATED" as object
(
  V_CONCAT varchar2(32767), -- concatenated strings

  static function ODCIAggregateInitialize(
    P_CONTEXT in out AT_CONCATCOMMASEPARATED)
  return number,

  member function ODCIAggregateIterate(
    self  in out AT_CONCATCOMMASEPARATED,
    P_VALUE in     varchar2)
  return number,

  member function ODCIAggregateTerminate(
    self     in  AT_CONCATCOMMASEPARATED,
    P_RESULT out varchar2,
    P_FLAGS  in  number)
  return number,

  member function ODCIAggregateMerge(
    self      in out AT_CONCATCOMMASEPARATED,
    P_CONTEXT in     AT_CONCATCOMMASEPARATED)
  return number
);

create or replace type body AT_CONCATCOMMASEPARATED is

static function ODCIAggregateInitialize(
  P_CONTEXT in out AT_CONCATCOMMASEPARATED)
return number
is
begin
  if P_CONTEXT is null then
    P_CONTEXT := AT_CONCATCOMMASEPARATED('');
  else
    P_CONTEXT.V_CONCAT := null;
  end if;

  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self    in out AT_CONCATCOMMASEPARATED,
  P_VALUE in     varchar2)
return number
is
begin
  if self.V_CONCAT is null then
    self.V_CONCAT := P_VALUE;
  else
    self.V_CONCAT := self.V_CONCAT || ', ' || P_VALUE;
  end if;

  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self     in  AT_CONCATCOMMASEPARATED,
  P_RESULT out varchar2,
  P_FLAGS  in  number)
return number
is
begin
  P_RESULT := substr(self.V_CONCAT, 0, 4000);

  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(
  self      in out AT_CONCATCOMMASEPARATED,
  P_CONTEXT in     AT_CONCATCOMMASEPARATED)
return number
is
begin
  if P_CONTEXT.V_CONCAT is not null then
    if self.V_CONCAT is null then
      self.V_CONCAT := P_CONTEXT.V_CONCAT;
    else
      self.V_CONCAT := self.V_CONCAT || ', ' || P_CONTEXT.V_CONCAT;
    end if;
  end if;

  return ODCIConst.Success;
end;

end;

create or replace function FNC_CONCATCOMMASEPARATED (input varchar2) return varchar2
aggregate using AT_CONCATCOMMASEPARATED;

然后插入语句变为:

Insert into    NEW_TFIDF_TABLE
SELECT T.ID, T.TERMS ||', '|| FNC_CONCATCOMMASEPARATED(TT.TERMS) 
FROM TFIDF_TABLE T
INNER JOIN TERMS_TABLE TT  ON  T.ID=TT.ID
GROUP BY t.ID, t.TERMS /* assuming the id is unique in t */

这篇关于内部联接两个表，聚合varchar字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！