我有三张表,父母、子女和孙子:
+----------------+ +----------------+ +---------------------+
| 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。
最佳答案
尝试此选择查询(它使用'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;
如果有效,我们将重写它以插入..select语句,或者尝试自己执行;)