求一sql语句

Question

有如下两张表
表A：
id  taskid area_code keyword advertiser_id order_month
1    1      0         咖啡      1             201108
2    2      1         音乐      2             201108
3    3      0         生活      3             201109
表B：
id  keyword   synonyms
 1    咖啡      咖啡厅
 2    咖啡      咖啡屋
 3    咖啡      咖啡店
 4    音乐      流行音乐
 5    音乐      古典音乐
 6    生活      恬静的生活
 7    生活      安逸的生活
两张表对应的字段是keyword
现在想要如下结果：
area_code keyword order_month synonyms
  0         咖啡    201108     咖啡厅,咖啡屋,咖啡店
  0         生活    201109     恬静的生活,安逸的生活
  1         音乐    201108     流行音乐,古典音乐

Answer 1

SQL> WITH t1 AS (
  2  SELECT '1' tid,'1' taskid,'0' area_code,'咖啡' keyword,'1' advertiser_id,'201108' order_month FROM dual UNION ALL
  3  SELECT '2' tid,'2' taskid,'1' area_code,'音乐' keyword,'2' advertiser_id,'201108' order_month FROM dual UNION ALL
  4  SELECT '3' tid,'3' taskid,'0' area_code,'生活' keyword,'3' advertiser_id,'201109' order_month FROM dual
  5  )
  6  ,t2 AS (
  7  SELECT 1 tid,'咖啡' keyword,'咖啡厅' synonyms FROM dual UNION ALL
  8  SELECT 2 tid,'咖啡' keyword,'咖啡屋' synonyms FROM dual UNION ALL
  9  SELECT 3 tid,'咖啡' keyword,'咖啡店' synonyms FROM dual UNION ALL
 10  SELECT 4 tid,'音乐' keyword,'流行音乐' synonyms FROM dual UNION ALL
 11  SELECT 5 tid,'音乐' keyword,'古典音乐' synonyms FROM dual UNION ALL
 12  SELECT 6 tid,'生活' keyword,'恬静的生活' synonyms FROM dual UNION ALL
 13  SELECT 7 tid,'生活' keyword,'安逸的生活' synonyms FROM dual
 14  )
 15  SELECT t1.area_code,
 16         t1.keyword,
 17         t1.order_month,
 18         n.synonyms
 19    FROM t1
 20    LEFT JOIN (SELECT m.keyword,
 21                      substr(sys_connect_by_path(synonyms, ','), 2) synonyms
 22                 FROM (SELECT t2.*,
 23                              row_number() over(PARTITION BY t2.keyword ORDER BY t2.tid) rn
 24                         FROM t2) m
 25                WHERE connect_by_isleaf = 1
 26                START WITH m.rn = 1
 27               CONNECT BY PRIOR rn = rn - 1
 28                      AND PRIOR keyword = keyword) n ON t1.keyword =
 29                                                        n.keyword
 30  ;AREA_CODE KEYWORD ORDER_MONTH SYNONYMS
--------- ------- ----------- --------------------------------------------------------------------------------
0         咖啡    201108      咖啡厅,咖啡屋,咖啡店
0         生活    201109      恬静的生活,安逸的生活
1         音乐    201108      流行音乐,古典音乐

Answer 2

CREATE OR REPLACE FUNCTION F_GROUP(IN_KEYWORD IN VARCHAR2) RETURN VARCHAR2 IS  STR_RETURN VARCHAR2(500);
  CURSOR C1(V_KEYWORD VARCHAR2) IS
    SELECT SYNONYMS FROM TB_B WHERE KEYWORD = V_KEYWORD;
BEGIN
  FOR STR_TEMP IN C1(IN_KEYWORD) LOOP
    STR_RETURN := STR_RETURN || STR_TEMP.SYNONYMS || CHR(44);
  END LOOP;
  STR_RETURN := RTRIM(STR_RETURN, CHR(44));
  RETURN STR_RETURN;
END F_GROUP;SELECT area_code, keyword, order_month,f_group(a.keyword) FROM tb_a a 

Answer 3

无须在增加一个row_number的函数了!直接写WITH t1 AS (
   SELECT '1' tid,'1' taskid,'0' area_code,'咖啡' keyword,'1' advertiser_id,'201108' order_month FROM dual UNION ALL
   SELECT '2' tid,'2' taskid,'1' area_code,'音乐' keyword,'2' advertiser_id,'201108' order_month FROM dual UNION ALL
   SELECT '3' tid,'3' taskid,'0' area_code,'生活' keyword,'3' advertiser_id,'201109' order_month FROM dual
   )
   ,t2 AS (
   SELECT 1 tid,'咖啡' keyword,'咖啡厅' synonyms FROM dual UNION ALL
   SELECT 2 tid,'咖啡' keyword,'咖啡屋' synonyms FROM dual UNION ALL
   SELECT 3 tid,'咖啡' keyword,'咖啡店' synonyms FROM dual UNION ALL
   SELECT 4 tid,'音乐' keyword,'流行音乐' synonyms FROM dual UNION ALL
   SELECT 5 tid,'音乐' keyword,'古典音乐' synonyms FROM dual UNION ALL
   SELECT 6 tid,'生活' keyword,'恬静的生活' synonyms FROM dual UNION ALL
   SELECT 7 tid,'生活' keyword,'安逸的生活' synonyms FROM dual
   )
   
 select t1.area_code,t1.keyword,t1.order_month,
max(substr(sys_connect_by_path(synonyms,','),2))
 from t1,t2 where t1.keyword=t2.keyword
 start with 1=1
  connect by t2.tid =PRIOR  t2.tid+1
  and t1.tid=PRIOR  t1.tid
 group by t1.area_code,t1.keyword,t1.order_month--result:0 咖啡 201108 咖啡屋,咖啡店
0 生活 201109 恬静的生活,安逸的生活
1 音乐 201108 流行音乐,古典音乐

Answer 4

row_number还是要加的，如果tid不是连续的，你的结果就不正确了。

Answer 5

row_number还是要加的，如果tid不是连续的，你的结果就不正确了。

Answer 6

那如果表A和表B都有N多数据了？

Answer 7

新版本人oracle提供的取合函数wm_concat可以更方便的完成这个需求。
注：下面的代码中引用了1楼的测试数据WITH t1 AS (
    SELECT '1' tid,'1' taskid,'0' area_code,'咖啡' keyword,'1' advertiser_id,'201108' order_month FROM dual UNION ALL
    SELECT '2' tid,'2' taskid,'1' area_code,'音乐' keyword,'2' advertiser_id,'201108' order_month FROM dual UNION ALL
    SELECT '3' tid,'3' taskid,'0' area_code,'生活' keyword,'3' advertiser_id,'201109' order_month FROM dual
    )
    ,t2 AS (
    SELECT 1 tid,'咖啡' keyword,'咖啡厅' synonyms FROM dual UNION ALL
    SELECT 2 tid,'咖啡' keyword,'咖啡屋' synonyms FROM dual UNION ALL
    SELECT 3 tid,'咖啡' keyword,'咖啡店' synonyms FROM dual UNION ALL
    SELECT 4 tid,'音乐' keyword,'流行音乐' synonyms FROM dual UNION ALL
    SELECT 5 tid,'音乐' keyword,'古典音乐' synonyms FROM dual UNION ALL
    SELECT 6 tid,'生活' keyword,'恬静的生活' synonyms FROM dual UNION ALL
    SELECT 7 tid,'生活' keyword,'安逸的生活' synonyms FROM dual
   )
SELECT area_code,t1.keyword,order_month,wm_concat(synonyms) 
from t1,t2 
where t1.keyword = t2.keyword
group by area_code,t1.keyword,order_month;

Answer 8

这是我在搜索到的测试成功。网上很多这种字符串聚合方法，这种比较好。ORACLE字符串的连接聚合函数可用于group by1.新建type strcat_type-- 定义类型 聚合函数的实质就是一个对象   
create or replace type strcat_type as object (   
    cat_string varchar2(4000),   
    --对象初始化   
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type)   
        return number,   
        --聚合函数的迭代方法(这是最重要的方法)   
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2)   
        return number,   
        --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合   
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)   
        return number,   
        --终止聚集函数的处理,返回聚集函数处理的结果   
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number)   
        return number   
) 2.建立type body strcat_type
create or replace type body strcat_type is  
  static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number   
  is  
  begin  
      cs_ctx := strcat_type( null );   
      return ODCIConst.Success;   
  end;   
  member function ODCIAggregateIterate(self IN OUT strcat_type,   
                                      value IN varchar2 )   
  return number   
  is  
  begin  
        /*字符串已','分割 */   
      self.cat_string := self.cat_string || ','|| value;   
      return ODCIConst.Success;   
  end;   
  member function ODCIAggregateTerminate(self IN Out strcat_type,   
                                        returnValue OUT varchar2,   
                                        flags IN number)   
  return number   
  is  
  begin
        /*去除空(is null)*/   
      returnValue := ltrim(rtrim(self.cat_string,','),',');   
      return ODCIConst.Success;   
  end;   
  member function ODCIAggregateMerge(self IN OUT strcat_type,   
                                    ctx2 IN Out strcat_type)   
  return number   
  is  
  begin  
      self.cat_string := self.cat_string || ',' || ctx2.cat_string;   
      return ODCIConst.Success;   
  end;
end;3.建立函数func_strcatCREATE OR REPLACE FUNCTION func_strcat(input varchar2)   
RETURN varchar2 -- 返回值   
PARALLEL_ENABLE AGGREGATE USING strcat_type; --使平行累加4.结果
select * from t_test t;id      keyword synonyms
1 咖啡 咖啡厅
2 咖啡 咖啡屋
3 咖啡 咖啡店
4 音乐 流行音乐
5 音乐 古典音乐
6 生活 恬静的生活
7 生活 安逸的生活
_________________________________________
select  t.keyword,func_strcat(t.synonyms)
from t_test t group by t.keyword;keyword func_strcat(t.synonyms)
咖啡 咖啡厅,咖啡屋,咖啡店
生活 恬静的生活,安逸的生活
音乐 流行音乐,古典音乐
__________________________________________
最后一步，将上面聚合结果和第一个a表联合即可