核心规则

GROUP BY 分组后，查询字段只能是分组字段、聚合函数字段，普通非分组字段直接查会报错。

几种可行写法

1. 多个分组字段

按多字段分组，即可查询对应字段

SELECT user_id,create_time,SUM(amount) AS total
FROM orders
GROUP BY user_id,create_time;

2. 聚合函数包裹其他字段

用MAX/MIN/COUNT等函数提取同组内字段

SELECT 
user_id,
MAX(order_id) AS last_order,
MIN(amount) AS min_money,
SUM(amount) AS total
FROM orders
GROUP BY user_id;

3. GROUP_CONCAT 拼接同组所有字段值

把组内多条数据的字段合并成字符串展示

SELECT 
user_id,
GROUP_CONCAT(order_id SEPARATOR ',') AS order_ids,
GROUP_CONCAT(amount SEPARATOR ',') AS money_list,
SUM(amount) AS total
FROM orders
GROUP BY user_id;

4. 关联子查询获取明细

先分组统计，再联表带出完整字段

SELECT o.*,t.total
FROM orders o
JOIN (
    SELECT user_id,SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t ON o.user_id = t.user_id;

SELECT o.*,t.total
FROM orders o
JOIN (
    SELECT user_id,SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    having(count(user_id)>1)
) t ON o.user_id = t.user_id;