实际生成中遇到的sql问题，记录一下。包含场景复现sql，隐藏实际数据。

解决方法
方法一：修改 GROUP BY 子句

将 sys_user.real_name、swimplat_member_record.cost_count 和 swimplat_member_ticket.type 添加到 GROUP BY
中。但是这可能会返回更多的行，具体取决于这些列的值是否唯一。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT sys_user.real_name AS 姓名,
       sys_user.phonenumber AS 电话,
       swimplat_member_record.cost_count AS 消费次数,
       swimplat_member_ticket.type AS 套票类型
FROM swimplat_member_record
LEFT JOIN swimplat_member_card
    ON swimplat_member_card.id = swimplat_member_record.member_card_id
LEFT JOIN swimplat_member
    ON swimplat_member.id = swimplat_member_card.member_id
LEFT JOIN sys_user
    ON sys_user.user_id = swimplat_member.user_id
LEFT JOIN swimplat_member_ticket
    ON swimplat_member_ticket.member_card_id = swimplat_member_card.id
WHERE sys_user.phonenumber IN (xxx, xxx, xxx, xxx)
GROUP BY sys_user.real_name, sys_user.phonenumber, swimplat_member_record.cost_count, swimplat_member_ticket.type;

方法二：使用聚合函数

如果你只想根据 phonenumber 分组，并且其他列的值需要聚合处理，可以使用聚合函数，如 MAX、MIN、SUM 等：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT MAX(sys_user.real_name) AS 姓名,
       sys_user.phonenumber AS 电话,
       MAX(swimplat_member_record.cost_count) AS 消费次数,
       MAX(swimplat_member_ticket.type) AS 套票类型
FROM swimplat_member_record
LEFT JOIN swimplat_member_card
    ON swimplat_member_card.id = swimplat_member_record.member_card_id
LEFT JOIN swimplat_member
    ON swimplat_member.id = swimplat_member_card.member_id
LEFT JOIN sys_user
    ON sys_user.user_id = swimplat_member.user_id
LEFT JOIN swimplat_member_ticket
    ON swimplat_member_ticket.member_card_id = swimplat_member_card.id
WHERE sys_user.phonenumber IN (xxx, xxx, xxx, xxx)
GROUP BY sys_user.phonenumber;

方法三：禁用 ONLY_FULL_GROUP_BY

可以通过禁用 ONLY_FULL_GROUP_BY SQL 模式来允许这种查询。但这并不推荐，因为它可能会导致意外的行为。要禁用它，可以在会话中运行以下命令：

1
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

或者全局禁用（需 MySQL 管理权限）：

1
SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

推荐方法

通常推荐方法二，即使用聚合函数。它能确保查询的逻辑和 GROUP BY 子句的要求相符，并避免全局禁用 ONLY_FULL_GROUP_BY 可能带来的问题。