SQL自连接查询和case...when详解

最新推荐文章于 2023-11-17 18:55:03 发布

码农脱贫

最新推荐文章于 2023-11-17 18:55:03 发布

阅读量1.2k

点赞数

分类专栏：数据库专栏文章标签： sql 自连接查询 case when 自连接

本文链接：https://blog.csdn.net/qq_35499112/article/details/86035251

版权

数据库专栏专栏收录该内容

13 篇文章 0 订阅

订阅专栏

今天在逛论坛，突然看到了一条sql语句，里面用了case和when，感觉自己以前没有学过这个知识点啊，就搜集了一些资料，下面是一些总结。

首先准备数据：

-- 创建数据库hiveDemo
CREATE DATABASE hiveDemo;
USE hiveDemo;
-- 创建course表
CREATE TABLE `course` (
  `id` INT,
  `sid` INT ,
  `course` VARCHAR(20),
  `score` INT 
) ;

-- // 字段解释：id, 学号， 课程， 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

需求：查询出所有数学课程成绩大于语文课程成绩的学生的学号和各科成绩

第一反应就是用自连接查询，就是把这一个表看成两张表，然后这两张表里分别只有数学成绩和语文成绩，在利用多表查询的知识，很完美就解决问题了。

自连接方法：

SELECT 
c1.sid,-- 学号
c1.course,-- shuxue课程
c1.score,-- 成绩
c2.course,-- yuwen课程
c2.score-- 语文成绩

FROM
	course c1,-- c1表只有shuxue成绩
	course c2-- c2表只有yuwen成绩
WHERE
	c1.sid=c2.sid-- 相当于外键关系（自己想象）
	AND
	c1.course="shuxue"-- 保证c1表的成绩只有shuxue成绩的条件
	AND
	c2.course="yuwen"-- 保证c2表的成绩只有yuwen成绩的条件
	AND
	c1.score>c2.score;-- shuxue成绩大于yuwem成绩

结果：

我个人还是感觉自连接查询更简单易懂，下面是这个题的case ...when版本（个人感觉超级复杂，此处只是为了演示）

第一步：-- 使用case...when...将不同的课程名称转换成不同的列

-- 使用case...when...将不同的课程名称转换成不同的列
CREATE VIEW tmp_course_view AS
SELECT sid, CASE course WHEN "shuxue" THEN score ELSE 0 END  AS shuxue,  
CASE course WHEN "yuwen" THEN score ELSE 0 END  AS yuwen FROM course;  

-- 查看转换后的表
SELECT * FROM tmp_course_view;

第二步：-- 以sid分组合并取各成绩最大值

-- 以sid分组合并取各成绩最大值
CREATE VIEW tmp_course_view1 AS
SELECT aa.sid, MAX(aa.shuxue) AS shuxue, MAX(aa.yuwen) AS yuwen FROM tmp_course_view aa GROUP BY sid;  
 -- 查看
SELECT * FROM tmp_course_view1;

第三步：-- 比较结果

--  比较结果
SELECT * FROM tmp_course_view1 WHERE shuxue > yuwen;

结果都没问题，那个更好用更好理解自己体会吧。

Case When Else End用法

更新和查询

/*更新*/
update table  
set 字段1=case     
    when 条件1 then 值1       
    when 条件2 then 值2      
    else 值3      
    end     
where    …… 


/*查询*/
select 字段1, 字段2,       
    case 字段3     
    when 值1 then 新值       
    when 值2 then 新值      
    end as 重新命名字段3的名字       
from table      
where ……      
order by ……