（课堂笔记）SQL 高级查询技巧：行列转换、重复数据、递归查询、连续登录

SQL高级查询技巧本笔记总结了SQL数据处理中的5个核心技巧1)行列转换使用CASE WHEN/PIVOT实现行转列UNION ALL/UNPIVOT实现列转行2)伪列应用ROWNUM分页查询ROWID精确去重3)重复数据处理通过ROW_NUMBER()时效性去重利用ROWID删除重复记录4)递归查询使用START WITH/CONNECT BY处理层级关系支持上下级双向查询5)连续登录分析采用日期-序号差值法识别连续日期通过三步计算连续天数。每种方法均提供标准语法模板和典型应用场景说明。 课堂笔记SQL 高级查询技巧本指南涵盖了数据处理中常用的几个核心技巧包括数据重塑、伪列应用、重复值处理、递归查询和连续问题分析。一、 行列转换核心思想同一份数据根据分析或展示的需要可以在行式存储和列式存储之间转换。1. 行转列 (多行 → 多列)场景将某个字段的不同值作为新的列头多行数据聚合到一行展示。三种方法对比方法语法/关键词优点缺点Plan ACASE WHEN标准SQL所有数据库通用语句较长列多时书写繁琐Plan BDECODE()Oracle 特有语法简洁数据库兼容性差Plan CPIVOT语法清晰专为此场景设计Oracle 11g 支持其他数据库语法不同示例将每个学生的多门成绩转成一行。sql-- 【核心示例】成绩表行转列 -- 原始数据张三 语文 98张三 数学 76... -- 目标结果张三 98 76 90 -- Plan A: CASE WHEN (推荐通用性最强) SELECT SNAME, SUM(CASE WHEN CLASS 语文 THEN SCORE END) AS 语文, SUM(CASE WHEN CLASS 数学 THEN SCORE END) AS 数学, SUM(CASE WHEN CLASS 英语 THEN SCORE END) AS 英语 FROM T_SCORE GROUP BY SNAME; -- Plan C: PIVOT (语法最清晰) SELECT * FROM T_SCORE PIVOT (SUM(SCORE) FOR CLASS IN (语文 AS 语文, 数学 AS 数学, 英语 AS 英语)); -- PIVOT 语法模板 -- PIVOT ( 聚合函数(指标列) FOR 待转换的列名 IN (列值1 AS 新列名1, 列值2 AS 新列名2...) )2. 列转行 (多列 → 多行)场景将一行中多个列的数据合并转换为一列中的多行数据。两种方法对比方法语法/关键词优点Plan AUNION ALL通用性强所有数据库都支持Plan BUNPIVOT语法简洁性能通常更好示例将每个人的成绩从一行转回多行。sql-- 【核心示例】成绩表列转行 -- 原始数据张三 98 76 90 -- 目标结果张三 语文 98 张三 数学 76... -- Plan A: UNION ALL SELECT SNAME, 语文 AS 科目, 语文 AS 成绩 FROM TT_SCORE UNION ALL SELECT SNAME, 数学 AS 科目, 数学 AS 成绩 FROM TT_SCORE UNION ALL SELECT SNAME, 英语 AS 科目, 英语 AS 成绩 FROM TT_SCORE; -- Plan B: UNPIVOT SELECT SNAME, 科目, 成绩 FROM TT_SCORE UNPIVOT (成绩 FOR 科目 IN (语文, 数学, 英语)); -- UNPIVOT 语法模板 -- UNPIVOT ( 数值列的列名 FOR 合并后列名 IN (原列头1, 原列头2, 原列头3...) )二、 伪列核心概念像表里的列但实际不存在是数据库自动生成的。ROWNUM作用为查询结果集动态分配行号从1开始。注意ROWNUM在WHERE判断时生成不能直接用于或。主要用途实现分页查询。sql-- 【分页模板】获取第6到第10条数据 SELECT * FROM ( SELECT ROWNUM AS RN, E.* FROM EMP E ) WHERE RN BETWEEN 6 AND 10; -- 正确用法取前N行 SELECT * FROM EMP WHERE ROWNUM 4; -- 错误用法WHERE ROWNUM 2 (无结果)ROWID作用表示数据行在数据库中物理存储的地址是唯一的18位字符串。主要用途删除重复记录时精确定位。三、 重复数据处理查询重复使用GROUP BYHAVING COUNT(1) 1。去重显示DISTINCTSELECT DISTINCT DEPTNO FROM EMP;GROUP BYSELECT DEPTNO FROM EMP GROUP BY DEPTNO;ROW_NUMBER()保留每组内最新的一条记录时效性去重。sql-- 【时效性去重】每个部门只保留入职最晚的那位员工信息 SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY HIREDATE DESC) AS RN, E.* FROM EMP E ) WHERE RN 1; -- 分组内按时间排序后序号为1的即是最新的一条删除重复利用ROWID的唯一性保留一条删除其余。sql-- 【删除重复模板】保留每个EMPNO下ROWID最大的那一行 DELETE FROM EMP WHERE ROWID NOT IN ( SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO -- 替换为标识重复的业务字段 ); -- COMMIT; -- 数据无价操作前务必备份四、 递归查询 (层次查询)场景处理表中自带的上下级关系如组织架构、物料清单。关键语法START WITH定义递归的根节点起点。CONNECT BY PRIOR定义父子关系的连接条件。LEVEL伪列表示当前数据所在的层级。规律向下查找下属CONNECT BY PRIOR 父 子向上查找领导CONNECT BY PRIOR 子 父sql-- 【核心示例1】从KING开始向下找所有下属 SELECT LEVEL, E.* FROM EMP E START WITH ENAME KING CONNECT BY PRIOR EMPNO MGR; -- PRIOR在父键旁表示向下遍历 -- 【核心示例2】从SMITH开始向上找所有领导 SELECT LEVEL, E.* FROM EMP E START WITH ENAME SMITH CONNECT BY PRIOR MGR EMPNO; -- PRIOR在子键旁表示向上遍历 -- 【高级用法】生成连续数据 (1到100的偶数) SELECT LEVEL AS ID FROM DUAL WHERE MOD(LEVEL, 2) 0 CONNECT BY LEVEL 100; -- 【高级用法】获取血缘关系链 SELECT LEVEL, SYS_CONNECT_BY_PATH(ENAME, -) AS 领导链条 -- 将路径用-拼接 FROM EMP START WITH ENAME KING CONNECT BY PRIOR EMPNO MGR;五、 连续登录问题场景计算用户连续登录的天数找出长期活跃用户。核心算法日期减去排序序号。如果日期连续登录日期 - 排序序号的差值相同。sql-- 【核心算法】计算每个用户的连续登录情况 WITH LOGIN_PREP AS ( SELECT USER_ID, LOGIN_DATE, -- 1. 按用户对登录日期排序生成序号 RN (1,2,3...) ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LOGIN_DATE) AS RN, -- 2. 核心日期减去序号差值相同的即为连续日期 LOGIN_DATE - ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LOGIN_DATE) AS DATE_DIFF FROM T_LOGIN ), CONTINUOUS_DAYS AS ( SELECT USER_ID, DATE_DIFF, COUNT(1) AS CONTINUOUS_CNT, -- 本次连续的天数 MIN(LOGIN_DATE) AS START_DATE, MAX(LOGIN_DATE) AS END_DATE FROM LOGIN_PREP GROUP BY USER_ID, DATE_DIFF ) -- 最终查询找出每个用户的最大连续天数和最近连续天数 SELECT USER_ID, MAX(CONTINUOUS_CNT) AS 最大连续天数, LAST_VALUE(CONTINUOUS_CNT) OVER (PARTITION BY USER_ID ORDER BY END_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 最近连续天数 FROM CONTINUOUS_DAYS GROUP BY USER_ID;解题三步走排序用ROW_NUMBER()为每次登录生成序号。做差计算登录日期 - 序号得到分组标识DATE_DIFF。聚合按用户和DATE_DIFF分组统计数量COUNT(1)即为连续天数。