SQL 一對多關聯查詢：JOIN 獲取扁平數據 + 應用層聚合 / 子查詢分頁優化

場景建模

用戶表 users 與作品表 works 為一對多關係，需查詢用戶及其所有作品，前端展示為用戶卡片內嵌作品列表。

方案一：JOIN 扁平查詢 + 代碼聚合（推薦中小數據量）

SELECT u.id, u.name, w.id AS work_id, w.title, w.cover
FROM users u
LEFT JOIN works w ON u.id = w.user_id
WHERE u.status = 1
ORDER BY u.id, w.created_at DESC;

應用層處理：遍歷結果集，按 user_id 分組構建巢狀結構：

Map map = new LinkedHashMap<>();
for (Row row : resultSet) {
    UserVO user = map.computeIfAbsent(row.getUserId(), id -> new UserVO(id, row.getUserName()));
    if (row.getWorkId() != null)
        user.getWorks().add(new WorkVO(row.getWorkId(), row.getTitle(), row.getCover()));
}
List users = new ArrayList<>(map.values());

方案二：分步查詢（適合大數據量/分頁）

1. 查用戶列表（分頁）：SELECT * FROM users WHERE status=1 LIMIT 20 OFFSET 0
2. 批量查作品：SELECT * FROM works WHERE user_id IN (?,?,...)
3. 內存映射組裝（同方案一）

方案三：子查詢 JSON 聚合（MySQL 5.7+/PostgreSQL）

SELECT u.*, 
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', w.id, 'title', w.title))
   FROM works w WHERE w.user_id = u.id) AS works
FROM users u WHERE u.status = 1;

選型建議