如何在SQL中结合GROUP BY将名和姓合并为全名

针对你需要从tbl_customers和tbl_payment表中，按customerNumber分组汇总总金额，同时拼接firstName和lastName为fullName的需求，以下是不同数据库环境下的具体实现方案：

核心思路 #

通过关联客户表与支付表，按客户编号分组，使用对应数据库的字符串拼接函数生成全名，再用SUM()聚合函数计算总支付金额。

1. MySQL/MariaDB 实现 #

使用CONCAT()函数拼接字符串，GROUP BY需包含所有非聚合字段（或依赖主键唯一性简化分组）：

SELECT 
    c.customerNumber,
    CONCAT(c.firstName, ' ', c.lastName) AS fullName,
    SUM(p.amount) AS totalAmount
FROM tbl_customers c
JOIN tbl_payment p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.firstName, c.lastName;

2. PostgreSQL 实现 #

支持||拼接符或CONCAT()函数，两种方式均可：

SELECT 
    c.customerNumber,
    c.firstName || ' ' || c.lastName AS fullName,
    -- 等价写法：CONCAT(c.firstName, ' ', c.lastName) AS fullName
    SUM(p.amount) AS totalAmount
FROM tbl_customers c
JOIN tbl_payment p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.firstName, c.lastName;

3. SQL Server 实现 #

推荐使用CONCAT()函数（自动处理NULL值），也可使用+拼接符（需注意NULL会导致结果为NULL）：

SELECT 
    c.customerNumber,
    CONCAT(c.firstName, ' ', c.lastName) AS fullName,
    -- 等价写法（需确保姓名非空）：c.firstName + ' ' + c.lastName AS fullName
    SUM(p.amount) AS totalAmount
FROM tbl_customers c
JOIN tbl_payment p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.firstName, c.lastName;

4. Oracle 实现 #

使用||拼接符，或嵌套CONCAT()函数（Oracle原生CONCAT仅支持两个参数）：

SELECT 
    c.customerNumber,
    c.firstName || ' ' || c.lastName AS fullName,
    -- 等价写法：CONCAT(CONCAT(c.firstName, ' '), c.lastName) AS fullName
    SUM(p.amount) AS totalAmount
FROM tbl_customers c
JOIN tbl_payment p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.firstName, c.lastName;

扩展：包含无支付记录的客户 #

如果需要显示所有客户（包括无支付记录的），将JOIN改为LEFT JOIN，并用COALESCE()将空金额转为0：

-- 以MySQL为例，其他数据库语法类似
SELECT 
    c.customerNumber,
    CONCAT(c.firstName, ' ', c.lastName) AS fullName,
    COALESCE(SUM(p.amount), 0) AS totalAmount
FROM tbl_customers c
LEFT JOIN tbl_payment p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.firstName, c.lastName;

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