602. 好友申请 II ：谁有最多的好友
RequestAccepted 表：
+----------------+---------+ | Column Name | Type | +----------------+---------+ | requester_id | int | | accepter_id | int | | accept_date | date | +----------------+---------+ (requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。 这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。
编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
示例 1：
输入： RequestAccepted 表： +--------------+-------------+-------------+ | requester_id | accepter_id | accept_date | +--------------+-------------+-------------+ | 1 | 2 | 2016/06/03 | | 1 | 3 | 2016/06/08 | | 2 | 3 | 2016/06/08 | | 3 | 4 | 2016/06/09 | +--------------+-------------+-------------+ 输出： +----+-----+ | id | num | +----+-----+ | 3 | 3 | +----+-----+ 解释： 编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多。进阶：在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？
select ids as id, count(*) as num from
(select requester_id as ids from requestaccepted
union all
select accepter_id from requestaccepted) a
group by ids
order by num desc
limit 1;
- 计算好友最多的用户就是发出以及接受最多好友申请的人，所以先把requester_id和accepter_id两列联合起来。
- 再通过id分组计算每个id出现出现的次数，根据计算的次数降序排序，取第一条就是好友人数最多的id。
585. 2016年的投资
Insurance 表：
+-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +-------------+-------+ pid 是这张表的主键(具有唯一值的列)。 表中的每一行都包含一条保险信息，其中： pid 是投保人的投保编号。 tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。 lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。 lon 是投保人所在城市的经度。题目数据确保 lon 不为空。
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
- 他在 2015 年的投保额 (
tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。 - 他所在的城市必须与其他投保人都不同（也就是说 (
lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。
示例 1：
输入： Insurance 表： +-----+----------+----------+-----+-----+ | pid | tiv_2015 | tiv_2016 | lat | lon | +-----+----------+----------+-----+-----+ | 1 | 10 | 5 | 10 | 10 | | 2 | 20 | 20 | 20 | 20 | | 3 | 10 | 30 | 20 | 20 | | 4 | 10 | 40 | 40 | 40 | +-----+----------+----------+-----+-----+ 输出： +----------+ | tiv_2016 | +----------+ | 45.00 | +----------+ 解释： 表中的第一条记录和最后一条记录都满足两个条件。 tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。 第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。 因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。
1.两个窗口函数
select round(sum(tiv_2016), 2) tiv_2016 from (
select
tiv_2016,
count(*) over(partition by tiv_2015) count_tiv_2015,
count(*) over(partition by lat, lon) count_lat_lon
from insurance
) as temp where count_lat_lon = 1 and count_tiv_2015 > 1
- 使用窗口函数计以tiv_2015分组算出收入数字个数。
- 使用窗口函数计以经纬度为分组计算出经纬度个数。
- 找出经纬度个数为1，tiv_2015大于1的行。
2.in函数
select round(sum(tiv_2016),2) as tiv_2016
from insurance
where tiv_2015 in (select tiv_2015 from insurance group by tiv_2015 having count(*)>1)
and (lat,lon) not in (select lat,lon from insurance group by lat,lon having count(*)>1)in函数会消耗性能。
185. 部门工资前三高的所有员工
表: Employee
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id 是该表的主键列(具有唯一值的列)。 departmentId 是 Department 表中 ID 的外键（reference 列）。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行表示部门ID和部门名。
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
示例 1:
输入: Employee 表: +----+-------+--------+--------------+ | 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 | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释: 在IT部门: - Max的工资最高 - 兰迪和乔都赚取第二高的独特的薪水 - 威尔的薪水是第三高的 在销售部: - 亨利的工资最高 - 山姆的薪水第二高 - 没有第三高的工资，因为只有两名员工
select b.name as department,a.name as employee,a.salary
from employee as a left join department as b on a.departmentId=b.id
where 3>(select count(distinct c.salary) from employee as c where a.salary<c.salary and a.departmentId=c.departmentId)
- 先找出原表中比自己大的数据，用count函数进行计数（要用distinct不然不符合题意）
- 筛选条件为计数结果不大于三（比自己的工资大的工资数不超过三，即比自己大的最多只有两个数，自己最差排第三）
- 连接两个表查询
1667. 修复表中的名字
表： Users
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | name | varchar | +----------------+---------+ user_id 是该表的主键(具有唯一值的列)。 该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。
编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
示例 1：
输入： Users table: +---------+-------+ | user_id | name | +---------+-------+ | 1 | aLice | | 2 | bOB | +---------+-------+ 输出： +---------+-------+ | user_id | name | +---------+-------+ | 1 | Alice | | 2 | Bob | +---------+-------+
select user_id, concat(upper(left(name,1)),lower(right(name,length(name)-1))) as name
from users
order by user_id
1. CONCAT() 函数
# CONCAT 可以将多个字符串拼接在一起。
2. LEFT(str, length) 函数# 从左开始截取字符串，length 是截取的长度。
3. UPPER(str) 与 LOWER(str)# UPPER(str) 将字符串中所有字符转为大写
# LOWER(str) 将字符串中所有字符转为小写
4. SUBSTRING(str, begin, end)# 截取字符串，end 不写默认为空。
# SUBSTRING(name, 2) 从第二个截取到末尾，注意并不是下标，就是第二个。
# CONCAT 用来拼接字符串 ● LEFT 从左边截取字符 ● RIGHT 从右边截取字符 ● UPPER 变为大写 ● LOWER 变为小写 ● LENGTH 获取字符串长度
521
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
