我的目标是创建一个UPDATE
语句,将父记录的default_page_id
更新为最早创建的子记录。
父级/子级是受UI限制的最大/深度2级;parent_id
列表示记录的父级,root
的type
值进一步简化了根/父级是什么。
我在这里摆弄了DDL/DML:https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=994d9bf3b20b3bbab21547d4334fc0e7
最终,我试图纠正的SELECT
将成为我的UPDATE
,但使用MIN(id)
,这是不可行的,因为id
是char(36)
,因此id
不会自动递增;我曾尝试使用MIN(created)
,但错过了GROUP BY的必要更新以解决此问题,我将不胜感激!
SELECT
*
FROM
(
SELECT
test_pages.id
FROM
test_pages
WHERE
test_pages.default_page_id is null
and test_pages.type = "root"
) as a
LEFT JOIN(
SELECT
parent_id ,
MIN(id) AS id
from
test_pages
WHERE
test_pages.type = "container"
GROUP BY
parent_id
) as b ON b.parent_id = a.id
SQL(SELECT(:
如果我正确理解这个问题,下面的sql可能会有所帮助:
SELECT
r.id,
r.default_page_id,
r.type,
r.title,
c.parent_id AS container_parent_id,
c.id AS container_id
FROM
test_pages AS r
LEFT JOIN (
SELECT
parent_id,
id
FROM
test_pages
WHERE
type = "container"
AND created = (
SELECT
MIN(created)
FROM
test_pages AS tp
WHERE
type = "container"
AND test_pages.parent_id = tp.parent_id
)
) AS c ON r.id = c.parent_id
WHERE
type = "root";
SQL(更新(:
更新sql如下:
UPDATE
test_pages AS t
SET
t.default_page_id = (
SELECT
id
FROM
(
SELECT
parent_id,
id
FROM
test_pages
WHERE
type = "container"
AND created = (
SELECT
MIN(created)
FROM
test_pages AS tp
WHERE
type = "container"
AND test_pages.parent_id = tp.parent_id
)
) AS c
WHERE
t.id = c.parent_id
);
输出:
更新后,test_pages
如下表所示:
id | version_id | >parent_id | default_page_id | type | itle创建已修改 |
---|---|---|---|---|---|
jf2039jf932032f | 2 | >abc1212312efffnan | container | hild Page 22022-02-23 23:25:41 | nan |
7eajiefaeaeaeailje | 2 | 8efjaliejfaelfjael | ootroot Page A | >nan;">nan | |
8efjaliejfaelfjael | 2 | 7eajiefaeaeaeailjean>容器Child Page Again 1 td style="text-align:center;">nan | |||
ejejejej | 2 | bc1212312efffnan | 容器 | >子页1:center;">nan||
fefeee9f9e9fee | 2 | nan>td style="text-align:center;">nanroot | 没有子级的根页面 | antd>||
jklfjealkjlfajfl | 2 | 7eajiefaeaeailje>td style="text align:centre!">nan容器 | 再次子页2 | 2022-02-07 23:25:41 | style="text-align:center;">nan