You need to enable JavaScript to run this app. SQL Server中如何将同一手机号的多行数据按呼叫排名转换为列显示
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.27 19:24:03