SQL - 《编程技术》

569 员工薪水中位数
182 查找重复的电子邮箱
- 182. 查找重复的电子邮箱">182. 查找重复的电子邮箱

569 员工薪水中位数

569. 员工薪水中位数 SQL 架构 Create table If Not Exists Employee (Id int, Company varchar(255), Salary int) Truncate table Employee insert into Employee (Id, Company, Salary) values (‘1’, ‘A’, ‘2341’) insert into Employee (Id, Company, Salary) values (‘2’, ‘A’, ‘341’) insert into Employee (Id, Company, Salary) values (‘3’, ‘A’, ‘15’)

insert into Employee (Id, Company, Salary) values (‘4’, ‘A’, ‘15314’)

insert into Employee (Id, Company, Salary) values (‘5’, ‘A’, ‘451’)

insert into Employee (Id, Company, Salary) values (‘6’, ‘A’, ‘513’)

insert into Employee (Id, Company, Salary) values (‘7’, ‘B’, ‘15’)

insert into Employee (Id, Company, Salary) values (‘8’, ‘B’, ‘13’)

insert into Employee (Id, Company, Salary) values (‘9’, ‘B’, ‘1154’)

insert into Employee (Id, Company, Salary) values (‘10’, ‘B’, ‘1345’)

insert into Employee (Id, Company, Salary) values (‘11’, ‘B’, ‘1221’)

insert into Employee (Id, Company, Salary) values (‘12’, ‘B’, ‘234’)

insert into Employee (Id, Company, Salary) values (‘13’, ‘C’, ‘2345’)

insert into Employee (Id, Company, Salary) values (‘14’, ‘C’, ‘2645’)

insert into Employee (Id, Company, Salary) values (‘15’, ‘C’, ‘2645’)

insert into Employee (Id, Company, Salary) values (‘16’, ‘C’, ‘2652’)

insert into Employee (Id, Company, Salary) values (‘17’, ‘C’, ‘65’)

Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

+——-+——————+————+

|Id | Company | Salary |

+——-+——————+————+

|1 | A | 2341 |

|2 | A | 341 |

|3 | A | 15 |

|4 | A | 15314 |

|5 | A | 451 |

|6 | A | 513 |

|7 | B | 15 |

|8 | B | 13 |

|9 | B | 1154 |

|10 | B | 1345 |

|11 | B | 1221 |

|12 | B | 234 |

|13 | C | 2345 |

|14 | C | 2645 |

|15 | C | 2645 |

|16 | C | 2652 |

|17 | C | 65 |

+——-+——————+————+

请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

+——-+——————+————+

|Id | Company | Salary |

+——-+——————+————+

|5 | A | 451 |

|6 | A | 513 |

|12 | B | 234 |

|9 | B | 1154 |

|14 | C | 2645 |

+——-+——————+————+

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/median-employee-salary

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

182 查找重复的电子邮箱

182. 查找重复的电子邮箱
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+——+————-+

| Id | Email |

+——+————-+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+——+————-+

根据以上输入，你的查询应返回以下结果：

+————-+

| Email |

+————-+

| a@b.com |

+————-+

说明：所有电子邮箱都是小写字母。

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/duplicate-emails

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

SQL 架构 Create table If Not Exists Person (Id int, Email varchar(255))

Truncate table Person

insert into Person (Id, Email) values (‘1’, ‘a@b.com’)

insert into Person (Id, Email) values (‘2’, ‘c@d.com’)

insert into Person (Id, Email) values (‘3’, ‘a@b.com’)

Method1

select Email from
(
    select Email, count(Email) as num 
    from Person
    group by Email
) as tmp
where num > 1;

Syntax

COUNT(column_name) 函数返回指定列的值的数目（NULL不计入）
SELECT COUNT(column_name) FROM table_name
SELECT COUNT(*) FROM table_name
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组
SELECT column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name

Method2

select Email from Person
group by Email
having count(Email) > 1;

Syntax

在SQL中增加HAVING子句原因是，WHERE关键字无法与合计函数一起使用
SELECT column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name
having aggregate_function(column_name) operator value
# 查找订单总金额少于2000的客户
SELECT Customer, SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000
# 希望查找客户“Bush”或“Adams”拥有超过1500的订单总额
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice) > 1500