如何用MySQL查询管理员、被终止用户及对应终止记录数据

需求说明 #

需要编写单条MySQL查询，一次性获取以下三类信息：

• 执行终止操作的管理员信息
• 被终止用户的核心数据
• 该用户对应的终止记录详情
  具体字段要求：
• users表：需获取被终止用户的id、username，执行操作的管理员的display_name，以及用于定位用户的serial
• terminations表：需获取该表所有字段（包含自增主键id、被终止用户IDuser、管理员IDadmin等）

期望返回格式 #

{
   "admin": "admin's display name", 
   "user": {
       "id": 1, 
       "username": "johndoe"
   }, 
   "term_details": {
       "id": 1, // `terminations`自增主键
       "reason": "reason" 
       // 其他`terminations`表字段...
   }
}

原查询问题 #

你尝试的单条查询未成功，核心问题是：仅关联了被终止用户的users记录，未关联管理员的users记录；且serial条件放在JOIN的ON子句中，可能导致返回不符合serial条件的无效数据。原查询如下：

SELECT 
   u.id, 
   u.display_name,
   t.*
FROM 
   terminations t
LEFT JOIN 
   users u
ON 
   u.id = t.user
AND u.serial = ?
WHERE
   t.state = "active" AND 
   t.expire_date > NOW()

修正后的查询语句 #

需要两次关联users表，分别获取被终止用户和管理员的信息，同时调整条件位置确保准确性：

SELECT
    admin_user.display_name AS admin,
    terminated_user.id AS user_id,
    terminated_user.username AS user_username,
    t.*
FROM
    terminations t
JOIN users terminated_user ON terminated_user.id = t.user
JOIN users admin_user ON admin_user.id = t.admin
WHERE
    terminated_user.serial = ?
    AND t.state = "active"
    AND t.expire_date > NOW()

查询解释 #

1. 两次关联users表：
   • terminated_user别名：关联被终止用户，获取其id、username和用于定位的serial
   • admin_user别名：关联执行操作的管理员，获取其display_name
2. 使用JOIN而非LEFT JOIN：确保只返回存在对应用户和管理员的有效终止记录
3. 将serial条件放在WHERE子句：精准过滤出目标用户的终止记录
4. 保留原有的terminations表状态和过期时间条件，确保只返回有效终止记录

如果需要直接映射到你期望的JSON结构，可以在应用层将查询结果进行字段映射，或者使用MySQL的JSON函数直接构造JSON输出：

SELECT
    JSON_OBJECT(
        'admin', admin_user.display_name,
        'user', JSON_OBJECT(
            'id', terminated_user.id,
            'username', terminated_user.username
        ),
        'term_details', JSON_OBJECT(
            'id', t.id,
            'user', t.user,
            'admin', t.admin,
            'reason', t.reason,
            'state', t.state,
            'expire_date', t.expire_date
            // 手动添加`terminations`表的其他字段
        )
    ) AS result
FROM
    terminations t
JOIN users terminated_user ON terminated_user.id = t.user
JOIN users admin_user ON admin_user.id = t.admin
WHERE
    terminated_user.serial = ?
    AND t.state = "active"
    AND t.expire_date > NOW()

注：若使用JSON_OBJECTAGG动态获取terminations所有字段，需确保MySQL版本在5.7及以上，且字段无冲突。

内容的提问来源于stack exchange，提问作者szkiddaj