mysql 原生查询中，distinct 与 order by 同时使用时若排序字段未出现在 select 列表中会报错；本文提供基于子查询的可靠解决方案，并详解原理、实现与注意事项。
mysql 原生查询中，distinct 与 order by 同时使用时若排序字段未出现在 select 列表中会报错；本文提供基于子查询的可靠解决方案，并详解原理、实现与注意事项。
在使用 Spring Data JPA 执行原生 SQL 查询时，一个常见误区是直接在含 DISTINCT 的查询中使用 ORDER BY 引用未被 SELECT 的字段（如 transaction_date）。正如示例中所示：
@Query(value = "SELECT DISTINCT transfer_to FROM transfers_table " +
"WHERE transfer_from = :transfer_from " +
"ORDER BY transaction_date DESC LIMIT 10", nativeQuery = true)
ArrayList<Integer> getTransferRequests(Integer transfer_from);该语句会触发 MySQL 错误：
Expression #1 of ORDER BY clause is not in SELECT list...
根本原因：MySQL（尤其在 sql_mode=ONLY_FULL_GROUP_BY 启用时）要求 ORDER BY 中出现的列必须满足以下任一条件：
- 出现在 SELECT 列表中；
- 是 GROUP BY 中的列；
- 是 DISTINCT 所选列的函数依赖项（MySQL 5.7+ 有限支持，但 transaction_date 显然不函数依赖于 transfer_to）。
因此，transaction_date 未被 SELECT，却用于排序，违反了 SQL 标准与 MySQL 严格模式约束。
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
✅ 正确解法：使用派生表（子查询）预排序 + 去重
先按时间倒序取最新记录（含所需排序字段），再对外层结果提取唯一 transfer_to —— 此时 ORDER BY 在子查询内生效，外层 DISTINCT 仅作用于已排序后的数据集，逻辑清晰且兼容所有 MySQL 版本：
SELECT DISTINCT transfer_to
FROM (
SELECT transfer_to, transaction_date
FROM transfers_table
WHERE transfer_from = :transfer_from
ORDER BY transaction_date DESC
LIMIT 100 -- 建议适当放宽上限，避免因去重后不足10条
) AS ranked
ORDER BY (SELECT NULL) -- 外层可省略 ORDER BY，因子查询已保序；如需强保证，可用此伪序占位（MySQL 8.0+ 支持窗口函数更优）
LIMIT 10;? 为什么 LIMIT 100 而非 10？
子查询中若直接 LIMIT 10，可能只取到 10 条记录，其中 transfer_to 恰好全重复，导致外层 DISTINCT 后结果少于预期。扩大子查询范围（如 LIMIT 100 或 200）可显著提升获取足量唯一 transfer_to 的概率。实际值应根据业务中 transfer_to 的重复密度调整。
? Spring Data JPA 完整实现示例：
@Repository
public interface HomePageRepository extends JpaRepository<TransferEntity, Integer> {
@Query(value = "SELECT DISTINCT transfer_to FROM (" +
" SELECT transfer_to, transaction_date " +
" FROM transfers_table " +
" WHERE transfer_from = :transfer_from " +
" ORDER BY transaction_date DESC " +
" LIMIT 200" +
") AS ranked " +
"LIMIT 10", nativeQuery = true)
List<Integer> getTransferRequests(@Param("transfer_from") Integer transferFrom);
}? 进阶建议（MySQL 8.0+）：
若数据库版本 ≥ 8.0，推荐使用窗口函数实现更精准的“每个 transfer_to 取最新一条”逻辑（即 top-N per group）：
SELECT transfer_to
FROM (
SELECT transfer_to,
ROW_NUMBER() OVER (
PARTITION BY transfer_to
ORDER BY transaction_date DESC
) AS rn
FROM transfers_table
WHERE transfer_from = :transfer_from
) ranked
WHERE rn = 1
ORDER BY transaction_date DESC
LIMIT 10;该写法语义明确、无需猜测 LIMIT 上限，且天然支持按最新时间全局排序。
⚠️ 注意事项总结：
- ❌ 避免在 DISTINCT 查询中 ORDER BY 非 SELECT 字段；
- ✅ 子查询方案兼容性最好，是当前最稳妥的通用解法；
- ⚠️ LIMIT 值需结合数据分布合理设置，避免结果不足；
- ? MySQL 8.0+ 优先考虑 ROW_NUMBER() 窗口函数，语义严谨、性能可控；
- ? 建议在数据库客户端（如 MySQL Workbench）中先验证子查询逻辑，再集成至代码。
通过理解 SQL 执行顺序与 MySQL 严格模式约束，你不仅能解决当前问题，更能规避同类陷阱，写出更健壮、可维护的原生查询。
