我有3个表父母,孩子和&形式的孙子：
+----------------+ +----------------+ +---------------------+
| parent | | child | | grandchild |
+----------------+ +----------------+ +---------------------+
| parent_id (PK) | | child_id (PK) | | grandchild_id (PK) |
| parent_data | | child_data | | grandchild_data |
+----------------+ | parent_id (FK) | | child_id (FK) |
+----------------+ +---------------------+
PK =自动递增主键.
FK =外键.
我想要一个存储过程,可以复制父表中的记录以及子表和孙表中的任何相关记录.我可以将父数据和子数据复制好,这是我正在努力的孙子表.这是我所拥有的：
CREATE FUNCTION sf_copy_parent(p_parent_id INT) RETURNS INT
BEGIN
DECLARE new_parent_id INT;
-- create new parent record
INSERT INTO parent(parent_data)
SELECT parent_data FROM parent
WHERE parent_id=p_parent_id;
SET new_parent_id=LAST_INSERT_ID();
-- copy child records
INSERT INTO child(child_data,parent_id)
SELECT child_data,new_parent_id FROM child
WHERE parent_id=p_parent_id;
-- copy grandchild records ???
-- return
RETURN new_parent_id;
END
我正在使用MysqL5.5,如果这很重要的话.
最佳答案
尝试这个SELECT查询(它使用’p_parent_id’和’new_parent_id’变量) –
SET @r1 = 1;
SET @child_id = NULL;
SET @r2 = 0;
SELECT c1.grandchild_data,c2.child_id FROM (
SELECT @r1 := if(c.child_id IS NULL OR c.child_id <> @child_id,@r1 + 1,@r1) rank,@child_id := c.child_id,c.child_id,g.grandchild_data FROM child c
JOIN grandchild g
ON c.child_id = g.child_id
WHERE
c.parent_id = p_parent_id
ORDER BY
c.child_id,g.grandchild_id
) c1
JOIN (SELECT @r2 := @r2 + 1 rank,child_id FROM child WHERE parent_id = new_parent_id ORDER BY child_id) c2
ON c1.rank = c2.rank;
如果它有效,我们将把它重写为INSERT..SELECT语句,或者尝试自己做;)