Mysql 子查询，最值查询

1.leetcode-184：查找部门内最高的薪水

首先是一个子查询，找出一个部门里最高的那个工资


随后查找最终需要的值，并且部门编号以及薪水应该包含在这个子查询中
最终答案：

# Write your MySQL query statement below
SELECT Department.name AS Department,
       Employee.name AS Employee,
       Salary
FROM Employee INNER JOIN Department 
ON Employee.departmentId = Department.id
WHERE 
    (Employee.DepartmentId,Salary) IN
    ( SELECT DepartmentId,MAX(Salary)
      FROM Employee
      GROUP BY DepartmentId
    );
1
2
3
4
5
6
7
8
9
10
11
12

2.leetcode-607 销售员（两次子查询）

# Write your MySQL query statement below
SELECT name 
FROM SalesPerson AS S
WHERE S.sales_id NOT IN (
    SELECT sales_id
    FROM Orders
    WHERE Orders.com_id = ( SELECT com_id
                            FROM Company
                            WHERE Company.name = 'RED'
    )
);
1
2
3
4
5
6
7
8
9
10
11

3.leetcode-585. 2016年的投资

# Write your MySQL query statement below
# roound(,2)四舍五入保留两位小数
SELECT round(SUM(Insurance.tiv_2016),2) AS tiv_2016
FROM  Insurance
WHERE Insurance.tiv_2015 IN ( SELECT tiv_2015
                              FROM Insurance 
                              GROUP BY tiv_2015
                              HAVING COUNT(tiv_2015 ) >=2)
AND CONCAT(Insurance.lat,' ',Insurance.lon) IN (  SELECT CONCAT(lat,' ',lon) 
                                              FROM Insurance
                                              GROUP BY lat,lon
                                              HAVING COUNT(*)=1)

;
1
2
3
4
5
6
7
8
9
10
11
12
13
14