SQL语句---特殊查询

SQL语句—特殊查询

1、行列转换

效率很低，但是笔试有的企业数据库考试会考，一定要记住

在上述基础上加一行求平均分：

现在将上述两个部分拼接在一起：用union,并用括号将下面的括起来
union：将多个查询结果拼接在一起，会去除两个表中的重复数据
而union all 保留两个表中所有的数据

1.1 行转列

有如下score表

1.1.1 统计每个人的各科分数及总分

编写SQL语句，得到如下结果：

使用CASE

select 
	student,
	max(CASE WHEN subject = 'Java' THEN score END) as Java,
	max(CASE WHEN subject = 'MySQL' THEN score END) as MySQL,
	max(CASE WHEN subject = 'HTML' THEN score END) as HTML,
	sum(score) as Total
from score
GROUP BY student

使用子查询

select student, 
	(select score from score s1 where s1.student = s.student and subject = 'Java') as Java,
	(select score from score s1 where s1.student = s.student and subject = 'MySQL') as MySQL,
	(select score from score s1 where s1.student = s.student and subject = 'HTML') as HTML,
	sum(score) as Total
from score s
group by student

1.1.2 统计各门课程的数据

查询各门课程的最高分，最低分，平均分，及格率，不及格率，如下表：

使用子查询

对于最高分、最低分、平均分，只需要使用聚合函数即可。

但及格率和不及格率计算很不方便，这里使用case生成两个辅助列。

select subject,
	max(score) as 最高分, 
	min(score) as 最低分, 
	avg(score) as 平均分, 
	sum(及格)/count(id) as 及格率, 
	sum(不及格)/count(id) as 不及格率
from (
	select *,
		case when score>=60 then 1 else 0 end as 及格,
		case when score<60 then 1 else 0 end as 不及格
	from score
) as s
group by subject

1.2 列转行

有如下表：

1.2.1 查询每个人的工资流水

编写SQL语句，得到如下结果：

使用UNION拼接

select name, 'Jan' as month, Jan
from salary 
union
select name, 'Feb' as month, Feb
from salary 
union
select name, 'Mar' as month, Mar
from salary 
order by name desc

2、按月统计

数据表

有如下order表：

统计每个月的总销售额

编写SQL语句，得到如下结果：

每月都有数据

直接根据月份进行分组，不需要额外生成月份。

select 
	DATE_FORMAT(time,'%Y-%m') as 月份, 
	count(id) 订单数,
	sum(count) 销售量,
	sum(count*salary) 销售额
from `order`
group by year(time), month(time)
order by time

有的月份没有数据

需要额外生成月份，再进行连接查询。

查询过去12个月，每月的销售额，如当前日期是2023/08/08，则查询2022/08~2022/09十二个月的数据。

可利用如下代码生成12个月的信息：

select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) as m UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 2 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 3 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 4 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 5 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 6 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 7 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 8 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 9 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 10 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 11 MONTH)) UNION
select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 12 MONTH))

将得到如下结果：

再与order表进行连接查询。

select 
	DATE_FORMAT(m,'%Y-%m') as 月份, 
	COUNT(id) 订单数,
	IFNULL(SUM(count), 0) 销售量,
	IFNULL(SUM(count*salary), 0) 销售额
from (
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) as m UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 2 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 3 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 4 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 5 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 6 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 7 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 8 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 9 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 10 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 11 MONTH)) UNION
	select LAST_DAY(SUBDATE(CURDATE(),INTERVAL 12 MONTH))
) as month
left join `order` on year(time) = year(m) and month(time) = month(m)
group by m