sql 分组 & 行列转换

sql 分组 & 行列转换

sql语句教程参考W3C School - SQL 教程 就够了

1、groupby(配合组合函数使用)

参考 groupby分组和Count, Sum()

合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句,根据一个或多个列对结果集进行分组

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

举个栗子,原表如下:

O_IdOrderDateOrderPriceCustomer
12008/12/291000Bush
22008/11/231600Carter
32008/10/05700Bush
42008/09/28300Bush
52008/08/062000Adams
62008/07/21100Carter

现在,我们希望查找每个客户的总金额(总订单):

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
CustomerSUM(OrderPrice)
Bush2000
Carter1700
Adams2000
2、Sql的行列转换 - 纵横表

参考Sql的行列(纵横表)转换

关于纵表和横表的相互转换问题,主要是考虑表的设计以及业务的需求

1)纵表转横表

假设我有一张纵表

姓名课程分数
张三语文98
张三数学89
张三物理78
李四语文79
李四数学88
李四物理100

我们要转成的横表是这样子的:

姓名语文数学物理
张三988978
李四7988100

既然这个表只有两列,那么可以根据姓名进行分组。先把姓名拼凑出来,后面的分数我们再想办法。

select 姓名 from scores group by 姓名 

分析:

  • 我们先拿到语文这个科目的分数。既然我们用到了group by 语句,这里肯定要用聚合函数来求分数。

  • 而且我们只需要语文这一科的成绩,分组出来的 一共有 3列 ,分别是语文、数学、物理 。 那么就需要判断科目来取分数

这里符合我们需求的 case 语句就登场了。它和c#中switch-case 作用一样。

case 字段
    when1 then 结果
    when2 then 结果2
    ...
    else 默认结果
end
select 姓名,SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文 from scores group by 姓名 

查询到的表为:

姓名语文
张三98
李四79

完整sql语句为:

select 姓名,
    SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文,
    SUM(case 课程 when  '数学' then 分数 else 0 end) as 数学,
    SUM(case 课程 when  '物理' then 分数 else 0 end) as 物理
from scores group by 姓名 
2)横表转纵表

我们先把刚刚转好的表,插入一个新表Scores2中。

select 姓名,
    SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文,
    SUM(case 课程 when  '数学' then 分数 else 0 end) as 数学,
    SUM(case 课程 when  '物理' then 分数 else 0 end) as 物理
    into scores2
from scores group by 姓名

上面这条select into语句会报错:> 1327 - Undeclared variable: scores2

参考https://stackoverflow.com/questions/2949653/select-into-and-undeclared-variable-error

Notes:

1)先创建scores2横表,再修改成Insert into select即可(保持要插入的数据元组和scores字段个数一致可以成功插入)

2)select into语句被Insert into select取代,参考insert into select语句的使用 & 常见问题

3)Insert into select语句在sql_modestrict下并不支持select的关联查询的插入操作

INSERT into scores2
select NAME,
    SUM(case course when  '语文' then score else 0 end) as 语文,
    SUM(case course when  '数学' then score else 0 end) as 数学,
    SUM(case course when  '物理' then score else 0 end) as 物理
from scores group by NAME

得到该表

在这里插入图片描述

select 姓名,
 '语文' as 课程,
 语文 as 分数
 from scores2 

在这里插入图片描述

还有两科的数据怎么办呢? 很简单,我们一个个都查出来,然后用 union all 把他们组合为一张表就可以了。

整体sql语句

SELECT `姓名`, '语文' as '课程',语文 as 分数 
from scores2
UNION
SELECT `姓名`, '数学' as '课程',数学 as 分数 
from scores2
UNION
SELECT `姓名`, '物理' as '课程',物理 as 分数 
from scores2
ORDER BY `姓名` desc
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值