SQL经典模式 - 行转列

本文介绍了一种使用SQL的casewhen语句结合groupby及聚合函数来解决复杂查询问题的方法,通过实例演示如何将分散的雇员信息整合并筛选出符合特定条件的数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

不知道读者有没有类似的遭遇,遇到下面这样的设计:

#雇员信息
create table employee (eId int, propName varchar(10), propName varchar(10));

insert into employee values (1, 'firstName','Calvin'),(1,'lastName','Zhou'),(1,'age','25'), (1,'gender','1')
insert into employee values (2, 'firstName','Grace'),(2,'lastName','Lai'),(2,'age','28'), (2,'gender','0')
insert into employee values (3, 'firstName','Morgan'),(3,'age','28');


#雇员考评
create table score (eId int, score int);

insert into score (1,85), (2,60), (3,90), (4,20);


#用一条SQL语句找出考评在 80分以上, 年龄在28岁以下,并且性别是男 的雇员所有信息

遇到这个需求的时候,开发人员一般都要在心里开始访问DBA的家人了,他妈怎么设计的表,干嘛不把employee的属性集中放一行,搞得都没法查

于是,一般的做法:
1.先找一遍score表,查出所有的 score>85的eId,
2.然后再去employee表把这些Id相关的信息都加到内存里面,比较属性名,拼成一个Employee对象,
3.最后再循环这个对象,对age,和gender属性进行过滤


这个过程麻烦的要死,但如果熟练掌握行转列的技巧,那么就是轻而易举一条语句就查出了所有复合要求的Employee对象


这个技巧就是 case when, 配合group by,以及聚合函数 max或者min 使用:

步骤一, case when, 将行拆到列上面来, 没有值的列用Null表示

select
eId,
case propName when 'firstName' then propValue else null end as firstName,
case propName when 'lastName' then propValue else null end as lastName,
case propName when 'age' then propValue else null end as age,
case propName when 'gender' then propValue else null end as gender
from employee;

步骤二:然后对表进行group by,每个eId一个组:

select
eId,
case propName when 'firstName' then propValue else null end as firstName,
case propName when 'lastName' then propValue else null end as lastName,
case propName when 'age' then propValue else null end as age,
case propName when 'gender' then propValue else null end as gender
from employee group by eId;

步骤三: 用max,过滤掉所有的null

select
eId,
max(case propName when 'firstName' then propValue else null end) as firstName,
max(case propName when 'lastName' then propValue else null end) as lastName,
max(case propName when 'age' then propValue else null end) as age,
max(case propName when 'gender' then propValue else null end) as gender
from employee group by eId;


有了上面这个employee的临时表,就好办了,在这个基础上,添加Score条件的过滤:


select
eId,
max(case propName when 'firstName' then propValue else null end) as firstName,
max(case propName when 'lastName' then propValue else null end) as lastName,
max(case propName when 'age' then propValue else null end) as age,
max(case propName when 'gender' then propValue else null end) as gender
from employee
where eId in (select eId from score where score>80)
group by eId;


最后再添加上employee本身条件28岁以下,男

select tmp.* from (..) as tmp where tmp.age<28 and tmp.gender=1
### 实现行转列功能的常见方法 在 SQL 中实现行转列的操作通常被称为行列转换(Pivot)。以下是几种常见的实现方式: #### 方法一:使用条件聚合 通过 `CASE` 和聚合函数可以手动实现行转列的效果。这种方法适用于大多数关系型数据库,包括 Hive。 ```sql SELECT user_id, MAX(CASE WHEN subject = 'MATH' THEN score END) AS MATH, MAX(CASE WHEN subject = 'ENGLISH' THEN score END) AS ENGLISH, MAX(CASE WHEN subject = 'CHINESE' THEN score END) AS CHINESE FROM score_1 GROUP BY user_id; ``` 此查询将每一门课程的成绩作为单独的一列显示[^1]。 --- #### 方法二:使用 LATERAL VIEW 和 UDTF 函数 对于像 Hive 这样的大数据处理工具,可以通过 `LATERAL VIEW` 结合内置的表生成函数(UDTF),如 `explode()` 或者 `split()` 来完成复杂的行列转换。 例如,在 Hive 中可以这样写: ```sql SELECT user_id, tag_new, COUNT(*) AS count_value FROM ( SELECT user_id, tags FROM table_name ) t LATERAL VIEW explode(split(tags, ',')) exploded_table AS tag_new GROUP BY user_id, tag_new; ``` 这里利用了 `LATERAL VIEW` 将逗号分隔的字符串拆分为多行数据,并进一步实现了行转列的需求[^2]。 --- #### 方法三:基于 JOIN 的实现 如果目标字段数量固定,则可以直接采用多次自连接的方式来构建所需的结构化输出。下面是一个例子展示如何从原始表中提取不同科目的成绩并将其映射到独立列上: ```sql SELECT t1.user_id, t1.score AS MATH, t2.score AS ENGLISH, t3.score AS CHINESE FROM (SELECT user_id, score FROM score_1 WHERE subject = 'MATH') AS t1 JOIN (SELECT user_id, score FROM score_1 WHERE subject = 'ENGLISH') AS t2 ON t1.user_id = t2.user_id JOIN (SELECT user_id, score FROM score_1 WHERE subject = 'CHINESE') AS t3 ON t1.user_id = t3.user_id; ``` 该方案特别适合于科目种类较少的情况,因为它依赖显式的子查询定义每种情况下的逻辑[^3]。 --- #### 方法四:Hive 特定的 PIVOT 替代方法 虽然标准 SQL 提供了一个名为 `PIVOT` 的关键字用于简化此类操作,但在某些环境下可能并不支持它;此时可考虑其他变通手段比如上述提到过的 CASE 当句配合 SUM() 聚集运算符来模拟效果[^4]。 --- #### 总结 以上介绍了四种主要的技术路线来进行SQL中的行转列变换——即运用有条件求和、借助侧视图扩展维度、依靠联接重组以及模仿专用指令的行为模式达成最终目地。具体选用哪一种取决于实际应用场景和个人偏好等因素影响下做出决定即可满足需求[^5]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值