SQL Server中如何将同一手机号的多行数据按呼叫排名转换为列显示
行转列实现方案(按手机号分组、按呼叫时间排名)
核心逻辑分为两步:
- 以手机号为分区,按呼叫时间升序为每条记录生成组内排名(即第1/2/3...次呼叫)
- 对排名结果做行转列,将同一手机号的多条记录拼接为单行多列展示
步骤1:生成组内呼叫排名
通用窗口函数写法,适配所有支持窗口函数的数据库(MySQL 8.0+、SQL Server、Oracle、PostgreSQL等):
SELECT phone_number, call_time, -- 可根据需求增加其他字段,如呼叫时长、呼叫类型、通话状态等 call_duration, -- 按手机号分组、呼叫时间升序生成连续排名 ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY call_time ASC) AS call_rank FROM call_record_table
注:如果存在同一手机号呼叫时间完全相同的情况,需在
ORDER BY后追加次级排序规则(如记录主键id),避免排名错乱。
步骤2:行转列实现
根据是否需要提前固定排名数量,分为两种实现方式:
2.1 固定排名数量(示例为最多3次呼叫)
通用写法(适配所有数据库)
通过CASE WHEN搭配聚合函数实现,兼容性最强:
SELECT phone_number, -- 第1次呼叫字段 MAX(CASE WHEN call_rank = 1 THEN call_time END) AS first_call_time, MAX(CASE WHEN call_rank = 1 THEN call_duration END) AS first_call_duration, -- 第2次呼叫字段 MAX(CASE WHEN call_rank = 2 THEN call_time END) AS second_call_time, MAX(CASE WHEN call_rank = 2 THEN call_duration END) AS second_call_duration, -- 第3次呼叫字段 MAX(CASE WHEN call_rank = 3 THEN call_time END) AS third_call_time, MAX(CASE WHEN call_rank = 3 THEN call_duration END) AS third_call_duration FROM ( -- 嵌套步骤1的排名查询 SELECT phone_number, call_time, call_duration, ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY call_time ASC) AS call_rank FROM call_record_table ) t GROUP BY phone_number
内置PIVOT写法(适配SQL Server、Oracle)
直接调用数据库原生行转列函数,语法更简洁:
SELECT * FROM ( SELECT phone_number, call_time, call_duration, ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY call_time ASC) AS call_rank FROM call_record_table ) t PIVOT ( MAX(call_time) FOR call_rank IN ([1], [2], [3]) -- 括号内为要展示的排名,可按需增减 ) AS pivot_result
2.2 动态排名数量(自动适配最大呼叫次数)
如果同一手机号的呼叫次数不固定,无需提前写死排名数,可通过动态SQL自动生成对应列,以下为MySQL实现示例:
-- 调大GROUP_CONCAT长度限制,避免排名多时被截断 SET SESSION group_concat_max_len = 102400; -- 动态拼接列生成语句 SET @dynamic_sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(CASE WHEN call_rank = ', call_rank, ' THEN call_time END) AS `', call_rank, '_call_time`, ', 'MAX(CASE WHEN call_rank = ', call_rank, ' THEN call_duration END) AS `', call_rank, '_call_duration`' ) ) INTO @dynamic_sql FROM ( -- 获取所有存在的排名值,避免生成无效列 SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY call_time ASC) AS call_rank FROM call_record_table ) t_rank; -- 拼接完整查询语句 SET @full_sql = CONCAT(' SELECT phone_number, ', @dynamic_sql, ' FROM ( SELECT phone_number, call_time, call_duration, ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY call_time ASC) AS call_rank FROM call_record_table ) t GROUP BY phone_number '); -- 执行动态SQL PREPARE stmt FROM @full_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
实现效果:某手机号只有1次呼叫记录时,第2、3次及以后的列自动返回NULL,符合需求。
内容的提问来源于stack exchange,提问作者SQL Novice Seeking help
相关产品推荐
相关产品推荐

