SQL查询实现：定位违反评论关闭规则的MESSAGE_ID

问题分析 #

我们需要从CO_MESSAGE_TEXT_LG表中筛选出那些关闭评论的用户是该评论发起参与者的MESSAGE_ID。根据业务规则：

• 每条问题评论对应一条STATUS='OPEN'记录（包含两个发起用户：USER_NAME_1和USER_NAME_2）和一条STATUS='CLOSED'记录（关闭用户固定在USER_NAME_1字段）
• 违规判定条件：关闭评论的用户（CLOSED记录的USER_NAME_1）属于该评论OPEN记录中的任意一个发起用户

SQL查询语句 #

SELECT DISTINCT c.MESSAGE_ID
FROM CO_MESSAGE_TEXT_LG c
INNER JOIN CO_MESSAGE_TEXT_LG o 
  ON c.MESSAGE_ID = o.MESSAGE_ID
WHERE c.STATUS = 'CLOSED'
  AND o.STATUS = 'OPEN'
  AND c.USER_NAME_1 IN (o.USER_NAME_1, o.USER_NAME_2);

逻辑说明 #

1. 自连接关联：将表自身分别作为「关闭记录集(c)」和「发起记录集(o)」，通过MESSAGE_ID关联同一评论的两条状态记录
2. 状态过滤：分别筛选出CLOSED状态的关闭操作记录，以及OPEN状态的评论发起记录
3. 违规校验：检查关闭记录中的操作用户是否存在于对应发起记录的两个用户字段中，满足该条件即为违规评论
4. 去重处理：使用DISTINCT避免因异常重复记录导致同一违规ID多次返回

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