如何用MySQL/ActiveRecord获取GROUP BY分组后的完整Name实例？

现有表结构与数据 #

id | synonym_id | name        | deprecated
1  | 45         | Amanita     | 0
2  | 45         | Amaniita    | 1
3  | 45         | Amunita     | 1
4  | 31         | Agaricus    | 0
5  | 31         | Agarica     | 1
6  |            | Agartha     | 1

规则与期望结果 #

• 共享同一synonym_id的记录属于同义词组
• 每组优先保留deprecated=0的记录；若组内无该状态记录（如无synonym_id的单条记录），则保留该组唯一记录

期望查询结果：

id | synonym_id | name        | deprecated
1  | 45         | Amanita     | 0
4  | 31         | Agaricus    | 0
6  |            | Agartha     | 1

现有方案的局限 #

此前通过拼接字符串取最小值的方式仅返回拼接后的字符串，无法获取完整的Name实例：

SELECT MIN(CONCAT(n.deprecated, ',', n.text_name, ',', n.id))
  FROM names n
  GROUP BY IF(synonym_id, synonym_id, -id);

对应的ActiveRecord实现：

Name.where(id: subquery_scope.select(:name_id)).
  select((Name[:deprecated].cast('char') + "," +
          Name[:id].cast('char')).minimum.as("cnc")).
  group("IF(synonym_id, synonym_id, -id)")

可行解决方案 #

MySQL 查询写法 #

先通过子查询获取每个分组需保留的记录ID，再关联原表获取完整数据：

SELECT n.*
FROM names n
INNER JOIN (
  SELECT 
    IF(synonym_id, synonym_id, -id) AS group_key,
    -- 优先选取非废弃记录，无则取分组内最小ID的记录
    CASE 
      WHEN COUNT(CASE WHEN deprecated = 0 THEN 1 END) > 0 
      THEN MIN(CASE WHEN deprecated = 0 THEN id END)
      ELSE MIN(id)
    END AS target_id
  FROM names
  GROUP BY group_key
) AS group_ids ON n.id = group_ids.target_id;

ActiveRecord 实现 #

方式一：通过JOIN关联子查询获取完整实例

# 构建子查询，计算每个分组的目标记录ID
subquery = Name.select(
  "IF(synonym_id, synonym_id, -id) AS group_key",
  ActiveRecord::Base.sanitize_sql_array([
    "CASE WHEN COUNT(CASE WHEN deprecated = 0 THEN 1 END) > 0 THEN MIN(CASE WHEN deprecated = 0 THEN id END) ELSE MIN(id) END AS target_id"
  ])
).group("group_key")

# 关联子查询获取完整Name记录
Name.joins("INNER JOIN (#{subquery.to_sql}) AS group_ids ON names.id = group_ids.target_id")

方式二：通过ID筛选获取完整实例（更简洁）

# 子查询获取所有目标记录ID
target_ids = Name.select(
  ActiveRecord::Base.sanitize_sql_array([
    "CASE WHEN COUNT(CASE WHEN deprecated = 0 THEN 1 END) > 0 THEN MIN(CASE WHEN deprecated = 0 THEN id END) ELSE MIN(id) END"
  ])
).group("IF(synonym_id, synonym_id, -id)")

# 根据ID筛选完整Name实例
Name.where(id: target_ids)

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