By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Note (`id` TEXT, `name` TEXT, `createTime` INTEGER);
INSERT INTO Note (`id`, `name`, `createTime`) VALUES
('a', 'note1', '1641356316896'),
('b', 'note2', '1641356316895');
CREATE TABLE Tag (`id` INTEGER, `name` TEXT, `createTime` INTEGER, `noteId` TEXT);
INSERT INTO Tag (`id`, `name`, `createTime`, `noteId`) VALUES
('1', 'tag1', '1641341968228', 'a'),
('2', 'tag2', '1641341968229', 'a');
SELECT DISTINCT n.*,
GROUP_CONCAT(t.name) OVER (
PARTITION BY n.id
ORDER BY t.createTime DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) tagNames
FROM Note n LEFT JOIN Tag t
ON t.noteId = n.id
ORDER BY n.createTime DESC;
| id | name | createTime | tagNames |
|---|---|---|---|
| a | note1 | 1641356316896 | tag2,tag1 |
| b | note2 | 1641356316895 | null |