4
回答
db2数据迁移问题求解,求大指点
华为云实践训练营,热门技术免费实践!>>>   
大家好,请教个问题,在db2数据库中(A.course是新增加的字段,上线要做数据库老数据迁移处理),有这样的表和数据如下

create table A(studentId integer,courses varchar(255));
create table B(studentId integer,course varchar(255));
insert into A(studentId ) values(1);
insert into A(studentId ) values(2);
insert into A(studentId ) values(3);
insert into B(studentId,course) values(1,'语文');
insert into B(studentId,course) values(1,'数学');
insert into B(studentId,course) values(2,'历史');
insert into B(studentId,course) values(2,'政治');
insert into B(studentId,course) values(4,'化学');
insert into B(studentId,course) values(4,'体育');

现要把B表相同studentId的course拼接成字串更新到
A表,其希望的结果:
studentId    courses
1,        '语文,数学'
2,        '历史,政治'
3,        NULL


sql语句该如何写?只能sql搞定,其他没权限,权限控制的严格,安全意识很重复,涉及到金钱,
并且生产上数据库量很大
不可能因为这个就让你在生产上创建函数或存储过程,或专门写个java方法,这三种方法不要想
用with as可以查询拼接,但update没行通
网上的方法都试过了,行不通才来问,求大神指点
DB2
举报
王家雄
发帖于1年前 4回/144阅
共有4个答案 最后回答: 1年前

现已可以组查询并接,只是不能update

查询 sql如下


WITH
t1 (id, studentId, course) AS
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY studentId ORDER BY course desc) AS ID,
    studentId,
    course
  FROM
    B
),
t2 (id, studentId, course) AS
(
  SELECT
    t1.id,
    t1.studentId,
    CAST(t1.course AS varchar(100)) AS course
  FROM
    t1
  WHERE
    t1.id = 1
  UNION ALL
  SELECT
    t1.id,
    t2.studentId,
    CAST( t1.course || ',' || t2.course AS varchar(100)) AS course
  FROM
    t1, t2
  WHERE
    t1.studentId = t2.studentId
    AND t1.id = (t2.id + 1)
)
SELECT
  studentId,
  course
FROM
  t2
WHERE
  NOT EXISTS (
    SELECT 1
    FROM t2 t22
    WHERE
      t2.studentId = t22.studentId
      AND t2.id <  t22.id
  );

给个Oracle的merge into,DB2也应该支持这种写法,可能语法有点不太一样

MERGE INTO a
USING(SELECT studentid, listagg(course,',') WITHIN GROUP(ORDER BY studentid) courses FROM b GROUP BY studentid) b
ON (a.studentid = b.studentid)
WHEN MATCHED THEN 
  UPDATE SET a.courses = b.courses
WHEN NOT MATCHED THEN 
  INSERT VALUES(b.studentid,b.courses);



顶部