Spring Boot原生分页SQL查询作物翻译列表及多语言覆盖方案

你设想的使用CASE WHEN实现翻译存在性横向展示的思路完全可行，结合Spring Boot分页原生查询的使用场景，可直接按以下逻辑改造现有查询：

核心实现逻辑 #

我们需要同时满足两个核心要求：

• 保持原有查询的分页范围：即只返回指定语言ID下存在翻译的作物记录，不改变原有结果的数据集边界
• 为每条作物记录补充两类统计信息：该作物一共有多少种语言的翻译、每个目标语言下该作物的翻译是否存在

条件聚合实现翻译状态标记 #

针对translationcropdetails表，按cropid做分组聚合，用CASE WHEN判断每个语言ID对应的记录是否存在，聚合后即可得到每列的布尔值标记：

SELECT
  cropid,
  -- 统计单作物覆盖的翻译语言总数
  COUNT(DISTINCT languageid) AS total_translated_lang_count,
  -- 逐语言判断翻译是否存在，列别名可直接和前端字段映射
  MAX(CASE WHEN languageid = 1 THEN TRUE ELSE FALSE END) AS lang_1_exists,
  MAX(CASE WHEN languageid = 2 THEN TRUE ELSE FALSE END) AS lang_2_exists,
  MAX(CASE WHEN languageid = 3 THEN TRUE ELSE FALSE END) AS lang_3_exists
FROM translationcropdetails
GROUP BY cropid

原理说明：当某个cropid下存在对应languageid的记录时，CASE语句会返回TRUE，经过MAX聚合后该列的结果即为TRUE；若不存在对应记录则返回FALSE，完全匹配示例输出的格式要求。

关联主查询保留分页范围 #

将上述聚合统计的子查询，和原有筛选指定语言的主查询做内关联，把统计字段拼接到每一行结果上，不会改变原有分页的数据集范围：

SELECT
  base.cropid,
  base.cropname,
  stat.total_translated_lang_count,
  stat.lang_1_exists,
  stat.lang_2_exists,
  stat.lang_3_exists
FROM (
  -- 原有基础查询：筛选指定语言下的作物翻译信息
  SELECT trcropid, cropname, cropid, languageid
  FROM translationcropdetails
  WHERE languageid = :languageId
) base
INNER JOIN (
  -- 上述按作物分组的翻译统计子查询
  SELECT
    cropid,
    COUNT(DISTINCT languageid) AS total_translated_lang_count,
    MAX(CASE WHEN languageid = 1 THEN TRUE ELSE FALSE END) AS lang_1_exists,
    MAX(CASE WHEN languageid = 2 THEN TRUE ELSE FALSE END) AS lang_2_exists,
    MAX(CASE WHEN languageid = 3 THEN TRUE ELSE FALSE END) AS lang_3_exists
  FROM translationcropdetails
  GROUP BY cropid
) stat ON base.cropid = stat.cropid

Spring Boot JPA 适配说明 #

直接替换原有@Query注解中的SQL即可，注意两个适配细节：

• 分页的countQuery保持原有逻辑，只对基础筛选的子查询做计数，不需要把统计字段纳入count计算逻辑，避免分页总数计算错误
• 如果暂时无法固定所有需要展示的语言ID列表，可以先仅返回total_translated_lang_count字段，前端通过维护好的语言枚举列表自行匹配展示，无需动态拼接SQL列

性能优化提示 #

• 给translationcropdetails表的cropid、languageid字段建立联合索引，可大幅降低分组聚合的查询耗时
• 若不需要带出作物的基础属性（如作物图片、生长周期），无需关联cropdetails表；如果需要上述基础字段，可再通过base.cropid = cropdetails.cropid的关联关系补充查询即可

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