sql 分组 & 行列转换
sql语句教程参考W3C School - SQL 教程 就够了
1、groupby(配合组合函数使用)
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句,根据一个或多个列对结果集进行分组
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
举个栗子,原表如下:
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/12/29 | 1000 | Bush |
2 | 2008/11/23 | 1600 | Carter |
3 | 2008/10/05 | 700 | Bush |
4 | 2008/09/28 | 300 | Bush |
5 | 2008/08/06 | 2000 | Adams |
6 | 2008/07/21 | 100 | Carter |
现在,我们希望查找每个客户的总金额(总订单):
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
Customer | SUM(OrderPrice) |
---|---|
Bush | 2000 |
Carter | 1700 |
Adams | 2000 |
2、Sql的行列转换 - 纵横表
关于纵表和横表的相互转换问题,主要是考虑表的设计以及业务的需求。
1)纵表转横表
假设我有一张纵表
姓名 | 课程 | 分数 |
---|---|---|
张三 | 语文 | 98 |
张三 | 数学 | 89 |
张三 | 物理 | 78 |
李四 | 语文 | 79 |
李四 | 数学 | 88 |
李四 | 物理 | 100 |
我们要转成的横表是这样子的:
姓名 | 语文 | 数学 | 物理 |
---|---|---|---|
张三 | 98 | 89 | 78 |
李四 | 79 | 88 | 100 |
既然这个表只有两列,那么可以根据姓名进行分组。先把姓名拼凑出来,后面的分数我们再想办法。
select 姓名 from scores group by 姓名
分析:
-
我们先拿到语文这个科目的分数。既然我们用到了group by 语句,这里肯定要用聚合函数来求分数。
-
而且我们只需要语文这一科的成绩,分组出来的 一共有 3列 ,分别是语文、数学、物理 。 那么就需要判断科目来取分数。
这里符合我们需求的 case 语句就登场了。它和c#中switch-case 作用一样。
case 字段
when 值1 then 结果
when 值2 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_mode
为strict
下并不支持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