SQL查询--关于查询的练习题

港控/mmm° 2023-10-12 08:03 38阅读 0赞

> 下面的练习题出自LeetCode：[https://leetcode-cn.com/problemset/database/][https_leetcode-cn.com_problemset_database]，有兴趣的可以去上面刷刷题

**练习题1：超过经理收入的员工**

![1158674-20190904223524396-77174429.png][]

![1158674-20190904223536372-1052071928.png][]

** 分析：**

使用sql自连接查询，因为员工Id和经理的ManagerId都在同一个表，使用自连接，可以把一张表看作2张表来使用，一张为员工表ygb，一张为经理表jlb；

查询思路就是：筛选出员工表的ManagerId与经理表的Id相等的记录，然后在这些记录中筛选员工表的Salary大于经理表的Salary的记录

SELECT *  FROM employee ygb,employee jlb WHERE ygb.ManagerId = jlb.Id

![1158674-20190904223737361-1823936165.png][]

SELECT *  FROM employee ygb,employee jlb WHERE ygb.ManagerId = jlb.Id AND ygb.Salary>jlb.Salary

![1158674-20190904223806375-1080256819.png][]

SELECT ygb.name AS Employee FROM employee ygb,employee jlb WHERE ygb.ManagerId = jlb.Id AND ygb.Salary>jlb.Salary

![1158674-20190904223829365-387120505.png][]

**练习题2：删除重复的电子邮箱**

![1158674-20190904223908354-4888713.png][]

**分析：**  
先使用自连接查询，用自身表和自己比较，来判断哪些重复邮箱

SELECT * FROM Person p1, Person p2

这样得到一个2个相同表的笛卡尔积，即两张表的组合结果

![1158674-20190904224021359-2089832411.png][]

可以分别查询p1.\*和p2.\*

![1158674-20190904224245379-1129835924.png][]

接着查重

SELECT p1.* FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id>p2.Id

然后删除重复数据即可

DELETE p1.* FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id>p2.Id

--------------------

2019-09-04 22:47:00

转载于:https://www.cnblogs.com/hanmk/p/11462071.html

[https_leetcode-cn.com_problemset_database]: https://leetcode-cn.com/problemset/database/
[1158674-20190904223524396-77174429.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223524396-77174429.png
[1158674-20190904223536372-1052071928.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223536372-1052071928.png
[1158674-20190904223737361-1823936165.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223737361-1823936165.png
[1158674-20190904223806375-1080256819.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223806375-1080256819.png
[1158674-20190904223829365-387120505.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223829365-387120505.png
[1158674-20190904223908354-4888713.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904223908354-4888713.png
[1158674-20190904224021359-2089832411.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904224021359-2089832411.png
[1158674-20190904224245379-1129835924.png]: https://img2018.cnblogs.com/blog/1158674/201909/1158674-20190904224245379-1129835924.png