mysql 刷题

627. 变更性别

交换律： a ^ b ^ c ==> a ^ c ^ b

任何数于0异或为任何数 ： 0 ^ n ==> n

相同的数异或为0: n ^ n ==> 0

只用一个 SQL 查询，将 sex 字段反转。

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

UPDATE salary
SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );

196. Delete Duplicate Emails

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com

Id 是这个表的主键。

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

继续之前，先简单看一下表的连接过程，这个搞懂了，理解WHERE条件就简单了

a. 从驱动表（左表）取出N条记录，left join 中的左表就是驱动表，right join 中的右表是驱动表。；
b. 拿着这N条记录，依次到被驱动表（右表）查找满足WHERE条件的记录；

所以，官方sql的过程就是:

a. 从表p1取出3条记录；
b. 拿着第1条记录去表p2查找满足WHERE的记录，代入该条件p1.Email = p2.Email AND p1.Id > p2.Id后，发现没有满足的，所以不用删掉记录1；
c. 记录2同理；
d. 拿着第3条记录去表p2查找满足WHERE的记录，发现有一条记录满足，所以要从p1删掉记录3；
e. 3条记录遍历完，删掉了1条记录，这个DELETE也就结束了。

183

Customers 表：

Id	Name
1	Joe
2	Henry
3	Sam
4	Max

Orders 表：

Id	CustomerId
1	3
2	1

查找没有订单的顾客信息：

select Name as Customers
from Customers left join Orders o
on Customers.Id = o.CustomerId
where o.CustomerId is null;

子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件：

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'rank'
from Scores a
order by a.Score DESC

185. 部门工资前三高的所有员工

SQL架构
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department 表包含公司所有部门的信息。

Id	Name
1	IT
2	Sales

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

思路： 对于Employee和Departmant连接之后每一行，计算他所属部门中是否只有两个以下的人工资高于他。

SELECT b.Name as Department, a.Name as Employee, a.Salary 
from Employee  a INNER JOIN  (
    SELECT  distinct  c.Id,  c.Name, a.Salary as m 
    FROM  Employee a LEFT JOIN Department c  on a.DepartmentId=c.Id
    where (SELECT count(*) from (
        select distinct DepartmentId,Salary 
         from Employee
           ) d where d.DepartmentId=a.DepartmentId and d.Salary>a.Salary
        )<=2
) b on a.DepartmentId=b.Id and a.Salary=b.m;