[请教一个比较难搞的sql查询问题]关于合并子表记录成一个字符串和主表列一起显示的查询

Question

主表
c1 c2 c3
1  X  Y
2  X  Z子表e1列外键是主表c1列
e0 e1 e2
8  1  你
9  1  好
10 2  吃
11 2  了
12 2  吗想显示成
1 X Y 你好
2 X Z 吃了吗

Answer 1

SQL 语句本身好像没有合并记录的功能，只能合并列。
一个解决方法是利用函数，如下：-- 数据准备
CREATE TABLE mt(
c1 NUMBER PRIMARY KEY,
c2 VARCHAR2(30),
c3 VARCHAR2(30));CREATE TABLE ct(
e0 NUMBER PRIMARY KEY,
e1 NUMBER REFERENCES mt(c1),
e2 VARCHAR2(30));INSERT INTO mt VALUES(1,'X','Y');
INSERT INTO mt VALUES(2,'X','Z');
INSERT INTO ct VALUES(8,1,'你');
INSERT INTO ct VALUES(9,1,'好');
INSERT INTO ct VALUES(10,2,'吃');
INSERT INTO ct VALUES(11,2,'了');
INSERT INTO ct VALUES(12,2,'吗');-- 创建函数
CREATE OR REPLACE FUNCTION concat_rows(p_parent_id NUMBER) RETURN VARCHAR2 IS
  v_result VARCHAR2(120);
BEGIN
  FOR rec IN (SELECT e2 FROM ct WHERE e1 = p_parent_id ORDER BY e0) LOOP
    v_result := v_result || rec.e2;
  END LOOP;
  return v_result;
END;-- SQL 语句
SELECT c1,c2,c3,concat_rows(c1) FROM mt;-- 输出
        C1 C2                             C3                             CONCAT_ROWS(C1)
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
         1 X                              Y                              你好
         2 X                              Z                              吃了吗

Answer 2

select t1.c1, replace(substr(MAX(sys_connect_by_path(t1.e2, ',')),2),',','') e2
from 
(
select a.*,b.*,row_number()over(partition by a.c1 order by b.e0) rn
from 主表 a,子表 b
where a.c1=b.e1
) t1
start with t1.c1=1
connect by t1.c1 = prior t1.c1
and t1.rn -1 = prior t1.rn
group by t1.c1;

Answer 3

我把三楼的SQL语句改了一下，现在结果正确了。主要问题是：一 start with不对；二 字符串处理有点问题。select t1.c1,
       replace(MAX(substr(sys_connect_by_path(t1.e2, ','), 2)), ',', '') e2
  from (select a.*,
               b.*,
               row_number() over(partition by a.c1 order by b.e0) rn
          from mt a, ct b
         where a.c1 = b.e1) t1
 start with t1.rn = 1
connect by t1.c1 = prior t1.c1
       and t1.rn - 1 = prior t1.rn
 group by t1.c1;总结一下：这种方式实际上也是使用函数，只不过是系统函数。优点是不用自己开发函数，不需要懂PL/SQL。缺点是语句比较复杂难懂。但是，非常佩服，三楼的SQL知识非常丰富，跟你学了不少知识。这里谢了。