SQL-外连接的用法(部分)
2022/2/23 2:21:32
本文主要是介绍SQL-外连接的用法(部分),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
用外连接进行行列转换(行→列)
courses表:
CREATE TABLE Courses (name VARCHAR(32), course VARCHAR(32), PRIMARY KEY(name, course)); INSERT INTO Courses VALUES('赤井', 'SQL入门'); INSERT INTO Courses VALUES('赤井', 'UNIX基础'); INSERT INTO Courses VALUES('铃木', 'SQL入门'); INSERT INTO Courses VALUES('工藤', 'SQL入门'); INSERT INTO Courses VALUES('工藤', 'Java中级'); INSERT INTO Courses VALUES('吉田', 'UNIX基础'); INSERT INTO Courses VALUES('渡边', 'SQL入门');
需转换成下表:
方法及分析如下:
/* 水平展开求交叉表(1):使用外连接 代码臃肿,不易于扩展,表头列数增加性能会恶化 */ SELECT C0.name, CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL入门", CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX基础", CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java中级" FROM (SELECT DISTINCT name FROM Courses) C0 LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'SQL入门' ) C1 ON C0.name = C1.name LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'UNIX基础' ) C2 ON C0.name = C2.name LEFT OUTER JOIN (SELECT name FROM Courses WHERE course = 'Java中级' ) C3 ON C0.name = C3.name; /* 水平展开(2):使用标量子查询 易于扩展,增加PHP列时,只需要增加: (SELECT '○' FROM Courses C4 WHERE course = 'PHP入门' AND C4.name = C0.name) AS "PHP入门" 缺点是在select语句中使用标量子查询(或关联子查询)时,开销相当大 */ SELECT C0.name, (SELECT '○' FROM Courses C1 WHERE course = 'SQL入门' AND C1.name = C0.name) AS "SQL入门", (SELECT '○' FROM Courses C2 WHERE course = 'UNIX基础' AND C2.name = C0.name) AS "UNIX基础", (SELECT '○' FROM Courses C3 WHERE course = 'Java中级' AND C3.name = C0.name) AS "Java中级" FROM (SELECT DISTINCT name FROM Courses) C0; /* 水平展开(3):嵌套使用CASE表达式 聚合函数:SUM/MAX/MIN/AVG/COUNT 聚合函数的执行结果也是标量值,因此可以像常量和普通列一样使用 这里将聚合函数的返回值作为CASE表达式的判断条件 */ SELECT name, CASE WHEN SUM(CASE WHEN course = 'SQL入门' THEN 1 ELSE NULL END) >= 1 THEN '○' ELSE NULL END AS "SQL入门", CASE WHEN SUM(CASE WHEN course = 'UNIX基础' THEN 1 ELSE NULL END) >= 1 THEN '○' ELSE NULL END AS "UNIX基础", CASE WHEN SUM(CASE WHEN course = 'Java中级' THEN 1 ELSE NULL END) >= 1 THEN '○' ELSE NULL END AS "Java中级" FROM Courses GROUP BY name;
用外连接进行行列转换(列→行)
Personnel表:
CREATE TABLE Personnel (employee varchar(32), child_1 varchar(32), child_2 varchar(32), child_3 varchar(32), PRIMARY KEY(employee)); INSERT INTO Personnel VALUES('赤井', '一郎', '二郎', '三郎'); INSERT INTO Personnel VALUES('工藤', '春子', '夏子', NULL); INSERT INTO Personnel VALUES('铃木', '夏子', NULL, NULL); INSERT INTO Personnel VALUES('吉田', NULL, NULL, NULL);
需转换成下表:
方法及分析如下:/*先创建一个孩子的视图/* 孩子主表
CREATE VIEW Children(child) AS SELECT child_1 FROM Personnel UNION SELECT child_2 FROM Personnel UNION SELECT child_3 FROM Personnel; 获取员工子女列表的SQL语句(没有孩子的员工也输出) */ SELECT EMP.employee, CHILDREN.child FROM Personnel EMP LEFT OUTER JOIN Children ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
版权声明:本文章部分内容转载自《SQL进阶教程》作者:MICK ,出版社:人民邮电出版社,
文章仅供学习交流使用,转载请附上原文出处链接和本声明。
这篇关于SQL-外连接的用法(部分)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-01基于Python+Vue开发的医院门诊预约挂号系统
- 2024-10-01基于Python+Vue开发的旅游景区管理系统
- 2024-10-01RestfulAPI入门指南:打造简单易懂的API接口
- 2024-10-01初学者指南:了解和使用Server Action
- 2024-10-01Server Component入门指南:搭建与配置详解
- 2024-10-01React 中使用 useRequest 实现数据请求
- 2024-10-01使用 golang 将ETH账户的资产平均分散到其他账户
- 2024-10-01JWT用户校验课程:从入门到实践
- 2024-10-01Server Component课程入门指南
- 2024-09-30Dnd-Kit学习:新手快速入门指南