几种主流数据库特有的SQL查询语句语法模板

大家好，欢迎来到IT知识分享网。

前天我们一起把《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 = ;）。

二、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