前天我们一起把《269条常用SQL查询语句》进行了归类，昨天我们又一起创建了《10类SQL查询语句语法结构模板》，今天我们应该开始拆解几种主流数据库特有查询语法。先不看细枝末节，我们先看下面：（所有代码块或表格均可左右滚动）

一、MySQL特有：GROUP_CONCAT函数

GROUP_CONCAT是MySQL独有的聚合函数，将同一个分组内的多行数据按照指定规则合并为单个字符串，能够解决一对多关系中数据展示冗余的问题，将同一分组的多个关联值紧凑地聚合在一个字段中，广泛应用在报表生成、数据汇总、标签合并等场景。

GROUP_CONCAT函数支持去重、排序和自定义分隔符，灵活性高，我们可根据业务需求调整其合并规则。

1、查询语法模板及详细注释

SELECT 
    -- 分组列：必须出现在GROUP BY中，用于定义“哪些行属于同一组”
    分组列1, 
    分组列2,
    -- 核心函数：GROUP_CONCAT
    GROUP_CONCAT(
        [DISTINCT] 目标列,  -- 可选参数：对目标列的值去重后再合并（去除重复数据）
        [ORDER BY 排序列 [ASC|DESC]],  -- 可选参数：合并前按指定列排序（ASC升序，DESC降序，默认ASC）
        [SEPARATOR '自定义分隔符']  -- 可选参数：指定拼接分隔符（默认是逗号','）
    ) AS 合并结果别名  -- 为合并后的结果指定一个别名（便于读取）
FROM 表名  -- 要查询的表
GROUP BY 分组列1, 分组列2;  -- 按分组列分组（必须与SELECT中的分组列完全一致）

2、创建模拟数据表及插入模拟数据

我们创建student_clubs表，用来存储学生参与的社团信息（一个学生可加入多个社团，存在一对多关系）：

注：有“篮球社”、“摄影社”重复项

3、应用场景示例及输出结果解读

示例1：基础合并（默认规则：保留重复值、逗号分隔、不排序）
场景：我们按学生分组，合并其加入的所有社团名称（包含重复项，使用默认逗号分隔）。

SELECT 
    student_id AS 学生ID,
    student_name AS 学生姓名,
    GROUP_CONCAT(club_name) AS 参与社团  -- 不指定参数，使用默认规则
FROM student_clubs
GROUP BY student_id, student_name;

输出结果：

结果解读：

• 张三和王五因重复加入社团，结果中保留了重复的社团名称（如：“篮球社”出现2次）；
• 分隔符默认使用逗号“,”，未指定排序，因此按数据在表中的存储顺序合并（张三的社团顺序为“篮球社→编程社→篮球社”）。

示例2：去重合并（排除重复值）
场景：我们合并社团名称时去除重复项，仅保留唯一值。

SELECT 
    student_id AS 学生ID,
    student_name AS 学生姓名,
    GROUP_CONCAT(DISTINCT club_name) AS 参与社团  -- 添加DISTINCT参数去重
FROM student_clubs
GROUP BY student_id, student_name;

输出结果：

结果解读：

• DISTINCT参数生效，重复的社团名称被去除（如：张三的“篮球社”仅保留1次）；
• 其他规则仍使用默认值（逗号分隔、不排序）。

示例3：排序合并（按指定列排序后合并）
场景：我们合并社团名称时，按加入时间（join_time）升序排序（先加入的社团排在前面）。

SELECT 
    student_id AS 学生ID,
    student_name AS 学生姓名,
    GROUP_CONCAT(
        DISTINCT club_name  -- 先去重
        ORDER BY join_time ASC  -- 按加入时间升序排序
    ) AS 参与社团
FROM student_clubs
GROUP BY student_id, student_name;

输出结果：

注：第一行“篮球社（2023-09-01）早于编程社（2023-09-05）”；第三行“摄影社（2023-09-03）早于编程社（2023-09-06）”

结果解读：

• ORDER BY join_time ASC确保合并后的社团按加入时间从早到晚排序；GROUP_CONCAT的排序仅影响合并后的字符串内部顺序，不改变分组结果的整体顺序（分组顺序由GROUP BY决定）；
• 结合DISTINCT参数，既去重又保证了顺序性，结果更符合业务逻辑（按时间顺序展示学生参与的社团）。

示例4：自定义分隔符（使用特殊符号分隔）
场景：我们合并社团名称时，用“、”作为分隔符，并按照社团名称首字母降序排序。

SELECT 
    student_id AS 学生ID,
    student_name AS 学生姓名,
    GROUP_CONCAT(
        DISTINCT club_name
        ORDER BY club_name DESC  -- 按社团名称首字母降序排序（如：“编程社”→“篮球社”）
        SEPARATOR '、'  -- 用“、”作为分隔符
    ) AS 参与社团
FROM student_clubs
GROUP BY student_id, student_name;

输出结果：

注：第一行：“编程社”（B）首字母在“篮球社”（L）之后（降序）；第三行：“编程社”（B）首字母在“摄影社”（S）之后（降序）

结果解读：

• SEPARATOR '、'将默认分隔符逗号改为“、”，使结果更易读，一目了然；
• ORDER BY club_name DESC按社团名称首字母降序排序（中文按拼音排序），满足特定展示需求。

示例5：合并多列数据（拼接多个字段为复杂格式）
场景：我们合并“社团名称+加入时间”为“社团名（时间）”的格式，用分号“;”分隔。

SELECT 
    student_id AS 学生ID,
    student_name AS 学生姓名,
    GROUP_CONCAT(
        DISTINCT CONCAT(club_name, '(', join_time, ')')  -- 用CONCAT拼接多列
        ORDER BY join_time ASC
        SEPARATOR ';'
    ) AS 社团及加入时间
FROM student_clubs
GROUP BY student_id, student_name;

输出结果：

结果解读：

• 先用CONCAT(club_name, '(', join_time, ')')将社团名称和加入时间拼接为“社团名（时间）”的格式（如：“篮球社(2023-09-01)”）；
• 再用GROUP_CONCAT合并这些格式字符串，实现多列数据的聚合展示，便于直观查看每个社团的加入时间。

小结：

GROUP_CONCAT函数能将分组内的多行数据“折叠”为单行字符串，通过DISTINCT（去重）、ORDER BY（排序）、SEPARATOR（自定义分隔符）等参数的组合，可实现多样化的聚合需求。

实际应用中，GROUP_CONCAT函数常用在：

• 展示用户的多个标签、订单包含的多个商品等一对多关系数据；
• 生成简洁的报表（如：将同一班级的学生姓名合并为一个字符串）；
• 拼接多列信息为结构化字符串（如：“社团+时间”、“商品+价格”）。

使用时，我们要注意：GROUP_CONCAT的结果长度受MySQL系统变量group_concat_max_len限制（默认1024字节），超长会被截断，我们可通过修改该变量调整其结果长度（如：SET GLOBAL group_concat_max_len = 102400;）。

二、PostgreSQL特有：JSONB函数

JSONB是PostgreSQL中二进制格式的JSON数据类型，相比普通JSON类型，支持索引且查询效率更高，非常适合存储半结构化数据（如：用户画像、商品属性、日志信息等）。

JSONB函数集提供了非常多的操作能力，包括：提取JSON中的键值、查询嵌套结构、修改JSON内容、拆分JSON数组等等，能够灵活处理动态变化的非结构化数据，无需预先定义严格的表结构。

1、常用JSONB函数语法模板及详细注释

注：补充jsonb_agg()函数，将多行数据聚合为 JSON 数组，在反向操作（行转JSON数组）中常用。

2、创建模拟数据表及插入模拟数据

我们创建product_details表，用来存储产品的动态属性（JSONB类型字段attributes）：

3、应用场景示例及输出结果解读

示例1：提取基础键值与嵌套键值
场景：我们查询产品名称、价格、品牌和电池续航时间。

SELECT 
    product_id AS 产品ID,
    attributes ->> 'name' AS 产品名称,  -- 提取name（文本类型，可直接使用）
    (attributes -> 'price')::INT AS 价格,  -- 提取price（JSONB类型，转换为整数）
    attributes #>> '{specs, brand}' AS 品牌,  -- 提取specs下的brand（文本类型）
    attributes #>> '{specs, battery}' AS 电池续航
FROM product_details;

输出结果：

结果解读：

• ->>直接返回文本类型（如：产品名称），无需转换即可作为字符串使用；
• ->返回JSONB类型（如：价格），需通过::INT转换为整数才能进行数值计算；
• #>>通过路径{specs, brand}获取嵌套在specs中的brand值，适用于多层级JSON结构。

示例2：查询包含指定标签的产品
场景：我们查询所有标签（tags）中包含“热销”的产品。

SELECT 
    product_id AS 产品ID,
    attributes ->> 'name' AS 产品名称
FROM product_details
-- 检查tags数组是否包含“热销”（@>表示左侧JSON包含右侧JSON）
WHERE attributes -> 'tags' @> '["热销"]'::jsonb;

输出结果：

结果解读：

• attributes -> 'tags'获取tags数组（JSONB类型），@> '["热销"]'::jsonb判断该数组是否包含“热销”元素；
• 该语法适用于筛选包含特定元素的JSON数组（如：标签、权限列表等）。

示例3：将JSON数组拆分为多行
场景：我们将每个产品的颜色（color）数组拆分为多行，展示产品与颜色的对应关系。

SELECT 
    product_id AS 产品ID,
    attributes ->> 'name' AS 产品名称,
    -- 将color数组拆分为多行，每行一个颜色（value为JSONB类型，需转为文本）
    jsonb_array_elements(attributes #> '{specs, color}') ->> 0 AS 颜色
    -- jsonb_array_elements(attributes #> '{specs, color}')::text AS 颜色
FROM product_details;

输出结果：

结果解读：

• attributes #> '{specs, color}'获取specs下的color数组（如：["白色", "黑色"]）；
• jsonb_array_elements将数组拆分为多行，每个元素对应一行记录（如：无线耳机的颜色拆分为2行）；
• 拆分后可按颜色维度统计（如：“白色产品有多少种”），或与其他表关联查询。

示例4：修改JSON中的值（更新或新增键）
场景：我们将产品1的价格改为999，并新增“重量”属性（值为“30g”）。

-- 步骤1：修改价格
UPDATE product_details
SET attributes = jsonb_set(attributes, '{price}', '999'::jsonb)  -- 路径为{price}，新值为999
WHERE product_id = 1;

-- 步骤2：新增“重量”属性
UPDATE product_details
SET attributes = jsonb_set(attributes, '{weight}', '"30g"'::jsonb)  -- 新增weight键，值为"30g"
WHERE product_id = 1;

-- 查看修改结果
SELECT product_id, attributes FROM product_details WHERE product_id = 1;

输出结果（attributes字段内容）：

{
  "name": "无线耳机", 
  "price": 999,  -- 价格已从899修改为999
  "specs": {"brand": "华为", "color": ["白色", "黑色"], "battery": "24小时"}, 
  "tags": ["降噪", "热销"], 
  "weight": "30g"  -- 新增的重量属性
}

结果解读：

• jsonb_set函数支持修改已有键（如：price）和新增不存在的键（如weight）；
• 字符串类型的新值需用双引号包裹（如："30g"），并通过::jsonb转换为JSONB类型；
• 该功能无需修改表结构即可扩展数据字段，适合属性动态变化的场景（如：电商商品的个性化属性）。

示例5：查询JSON数组的长度
场景：我们查询每个产品的颜色选项数量（即color数组的长度）。

SELECT 
    product_id AS 产品ID,
    attributes ->> 'name' AS 产品名称,
    -- 先获取color数组，再通过jsonb_array_length计算长度
    jsonb_array_length(attributes #> '{specs, color}') AS 颜色选项数量
FROM product_details;

输出结果：

结果解读：

• jsonb_array_length：计算JSON数组的元素数量，此处通过该函数快速获取每个产品的颜色选项数；
• 类似函数还有jsonb_typeof（判断JSON值的类型，如：“string”、“array”）等，可进一步扩展JSON处理能力。

小结：

PostgreSQL对JSON数据类型有原生支持，其中JSONB是二进制格式的JSON类型（支持索引、效率更高），提供了非常多的函数用于JSON数据的查询、修改和分析，适用于存储半结构化数据（如：用户画像、日志信息等）。JSONB函数处理半结构化数据的优势在于：

1. 灵活性：无需预先定义表结构，可动态存储和扩展属性（如：产品的可变规格）；
2. 高效性：支持索引（如：GIN索引），查询性能优于普通JSON类型；
3. 功能性：通过丰富的函数实现键值提取、数组拆分、内容修改等操作，满足多样化需求。

实际应用中，JSONB函数常用在存储用户配置、日志信息、电商商品属性等场景，尤其适合数据结构频繁变化或难以预先定义的业务场景。

三、SQL Server特有：PIVOT运算符

PIVOT是SQL Server专门用来实现“行转列”转换的运算符，能将表中某一列的不同行值（如：“月份”、“类别”）转换为新的列名，并通过聚合函数（如：SUM、AVG）计算对应的值，主要用于生成交叉报表或多维度数据对比表，将原本横向分布的数据纵向展示（如：将“每个地区的季度销售额”转换为“以季度为列、地区为行”的报表），使数据更直观易读。

1、查询语法模板及详细注释

-- 最终查询结果包含的列：非聚合列（保持为行）+ 转换后的新列（原行值）
SELECT 非聚合列1, 非聚合列2, [新列1], [新列2], ..., [新列N]
FROM (
    -- 子查询：定义需要转换的基础数据（必须包含三类核心列）
    SELECT 
        分组列,  -- 保持为行的列（如：“地区”、“部门”，不参与转换）
        行值列,  -- 需要转换为列名的列（如：“季度”、“月份”，其值将成为新列名）
        聚合值列  -- 用于填充新列的数值（如：“销售额”、“数量”，需通过聚合函数计算）
    FROM 源表  -- 原始数据所在的表
    -- 可选：添加WHERE子句过滤数据（如：只保留2023年的数据）
    WHERE 过滤条件
) AS 子查询别名  -- 子查询必须指定别名
PIVOT (
    -- 聚合函数：对聚合值列进行计算（常用SUM、AVG、COUNT等）
    聚合函数(聚合值列)
    -- FOR子句：指定行值列，并定义其行值对应的新列名
    FOR 行值列 IN (
        [新列1],  -- 对应行值列的某个值（如：“Q1”、“1月”）
        [新列2],  -- 对应行值列的另一个值（如：“Q2”、“2月”）
        ...
        [新列N]   -- 所有需要转换的行值都必须手动列举
    )
) AS pivot_table别名;  -- 转换后的结果集必须指定别名

语法元素解析

（1）子查询（基础数据集）

• 作用：定义PIVOT操作的原始数据，需包含三类关键列：
• 分组列：转换后仍作为行标识的列（不参与行转列，如：“姓名”、“日期”）。
• 行转列的基准列：其值将被转换为新的列名（如：“课程名称”、“月份”）。
• 需要聚合的列：将被聚合函数计算后填充到新列中的数据（如：“分数”、“销售额”）。
• 示例：若原始表是student_scores，子查询可提取学生姓名（分组列）、课程（基准列）、分数（聚合列）。

（2）PIVOT关键字后的聚合函数

• 作用：对“需要聚合的列”进行计算，生成新列的值。
• 支持的函数：SUM、COUNT、AVG、MAX、MIN等，需根据业务场景选择。
• 示例：SUM(分数)表示对同一学生、同一课程的分数求和（若存在重复数据）。

（3）FOR [行转列的基准列]

• 作用：指定以哪一列的“值”作为转换后新列的名称。
• 限制：基准列的类型通常为字符串或数字（需能作为列名），且其值需在IN子句中明确枚举。

（4）IN ([值1], [值2], ...)

• 作用：明确列出基准列中需要转换为新列的具体值，未列出的值将被忽略。
• 注意事项：
• 若值中包含特殊字符（如：空格、数字开头），需用方括号[]包裹（如：[语文]、[1月]）。
• 可通过AS为新列指定别名（如：[语文] AS 语文成绩）。
• 示例：IN ([语文], [数学], [英语])表示将“课程”列中的“语文”、“数学”、“英语”转换为新列。

（5）最终SELECT子句

• 作用：从PIVOT转换后的结果集中选择需要的列。
• 可包含：
• 子查询中的“分组列”（作为行标识）。
• IN子句中定义的新列（由PIVOT生成）。

注意事项：
（1）IN子句必须明确枚举所有需要转换的基准列值，无法动态生成（若值不固定，需用动态SQL）。
（2）子查询中不能包含多余的列，否则可能导致聚合错误（仅保留分组列、基准列、聚合列）。
（3）若基准列的值重复（如：同一地区同一月份有多个销售额记录），聚合函数（如：SUM）会自动合并计算。

我们通过PIVOT，可以快速将行数据转换为更直观的列结构，适合报表生成、数据汇总等业务场景。

2、创建模拟数据表及插入模拟数据

我们创建region_sales表，用来存储各地区的季度销售额数据：

3、应用场景示例及输出结果解读

示例1：基础行转列（固定列名，单分组列）
场景：我们将“季度”行转换为列（Q1、Q2、Q3、Q4），展示各地区2023年的季度销售额。

SELECT 
    region AS 地区,
    [Q1] AS 一季度销售额,  -- 对应原quarter列的“Q1”
    [Q2] AS 二季度销售额,
    [Q3] AS 三季度销售额,
    [Q4] AS 四季度销售额
FROM (
    -- 子查询：提取地区、季度、销售额（过滤2023年数据）
    SELECT region, quarter, amount
    FROM region_sales
    WHERE year = 2023
) AS source_data
PIVOT (
    SUM(amount)  -- 对销售额求和（因每个地区+季度唯一，SUM等效于直接取值）
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])  -- 将季度行值转换为列名
) AS pivot_sales;

输出结果：

注：华北无Q4数据，显示NULL；华南无Q3、Q4数据。实际业务中可通过ISNULL函数替换为默认值（如：0，示例：ISNULL([Q4], 0) AS 四季度销售额）

结果解读：

• 子查询中，region是分组列（保持为行），quarter是行值列（转换为列名），amount是聚合值列（填充新列）；
• PIVOT将quarter列的“Q1”、“Q2”等行值转换为新列名，并用SUM(amount)计算对应的值；
• 无数据的单元格自动显示NULL（可通过ISNULL函数替换为0，如：ISNULL([Q4], 0) AS 四季度销售额）。

示例2：多分组列的行转列（按多个维度分组）
场景：按“年份+地区”分组，我们将“季度”转换为列，展示各年份各地区的季度销售额。

SELECT 
    year AS 年份,
    region AS 地区,
    [Q1] AS 一季度销售额,
    [Q2] AS 二季度销售额,
    [Q3] AS 三季度销售额
FROM (
    -- 子查询：包含年份、地区、季度、销售额（多了一个分组列year）
    SELECT year, region, quarter, amount
    FROM region_sales
    WHERE quarter IN ('Q1', 'Q2', 'Q3')  -- 只保留Q1-Q3
) AS source_data
PIVOT (
    SUM(amount)
    FOR quarter IN ([Q1], [Q2], [Q3])
) AS pivot_sales
ORDER BY year, region;

输出结果：

结果解读：

• 相比示例1，子查询增加了year作为分组列，最终结果按“年份+地区”两级维度展示；
• 多分组列适用于需要多层级汇总的场景（如：“年份→地区→季度”的三级数据）。

示例3：使用不同聚合函数（非SUM场景）
场景：统计各地区每个季度的销售记录数（而非销售额），我们将季度转换为列。

SELECT 
    region AS 地区,
    [Q1] AS Q1记录数,
    [Q2] AS Q2记录数,
    [Q3] AS Q3记录数,
    [Q4] AS Q4记录数
FROM (
    SELECT region, quarter, amount  -- amount仅用于存在性判断，不参与计算
    FROM region_sales
) AS source_data
PIVOT (
    COUNT(amount)  -- 用COUNT统计记录数（只要amount非NULL就计数）
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivot_count;

输出结果：

注：华北Q4无记录，COUNT返回0，等等

结果解读：

• PIVOT支持多种聚合函数，此处COUNT(amount)统计每个地区每个季度的记录数量；
• 与SUM不同，COUNT对空值（无记录）返回0，更适合统计“次数”、“数量”类指标。

示例4：动态生成列（适应行值不固定的场景）
场景：自动识别表中所有存在的季度（无需手动列举），动态转换为列（适用于季度值可能新增的场景）。

DECLARE 
    @columns NVARCHAR(MAX),  -- 存储动态列名（如：[Q1],[Q2],[Q3],[Q4]）
    @sql NVARCHAR(MAX);      -- 存储动态生成的SQL语句

-- 步骤1：动态获取所有季度作为列名（用QUOTENAME处理特殊字符，STRING_AGG拼接）
SELECT @columns = STRING_AGG(QUOTENAME(quarter), ',') 
FROM (SELECT DISTINCT quarter FROM region_sales) AS quarters;

-- 步骤2：拼接动态SQL（将@columns代入PIVOT语句）
SET @sql = N'
SELECT region AS 地区, ' + @columns + N'
FROM (
    SELECT region, quarter, amount FROM region_sales
) AS source_data
PIVOT (
    SUM(amount) FOR quarter IN (' + @columns + N')
) AS pivot_sales;';

-- 步骤3：执行动态SQL
EXEC sp_executesql @sql;

说明：STRING_AGG函数SQL Server 2017 +支持的，若需兼容低版本，可使用FOR XML PATH拼接列名的替代方案，如：SELECT @columns = STUFF((SELECT ',' + QUOTENAME(quarter) FROM (SELECT DISTINCT quarter FROM region_sales) AS t FOR XML PATH('')), 1, 1, '')

输出结果：

结果解读：

• 当行值（如：季度）不固定时（如：新增“Q5”），静态PIVOT需要手动修改列名，而动态SQL通过STRING_AGG自动获取所有行值，无需修改代码；
• QUOTENAME(quarter)将列名转换为[Q1]格式，避免特殊字符（如：空格、数字开头）导致的语法错误；
• 动态SQL适用于行值频繁变化的场景（如：按月份、产品类型动态生成报表）。

小结

PIVOT运算符将行数据转换为列数据，简化了交叉报表的生成过程，关键特性如下：

1. 必须通过子查询定义三类核心列：分组列（保持为行）、行值列（转换为列名）、聚合值列（填充新列）；
2. 支持SUM、COUNT、AVG等多种聚合函数，根据业务需求选择；
3. 静态PIVOT适合行值固定的场景，动态SQL适合行值灵活变化的场景。

实际应用中，PIVOT运算符常用在生成年度销售报表、地区业绩对比表、多维度数据分析等场景，使数据展示更符合我们的阅读习惯。

说到底，这些主流数据库查询语法的差异，更像方言而非外语。在掌握通用SQL查询语句的基础上，我们再有针对性记些 “方言”，如：Oracle的ROWNUM、PostgreSQL的JSONB、SQL Server的PIVOT及MySQL的GROUP_CONCAT，等等，还有LIMIT这样的一些细枝末节，就够用了。真遇到生僻场景，我们查手册比死记硬背更高效。

阅读原文：原文链接