leetcode Database2 (四)

、Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

分析:编写一个SQL查询从Person表中找出所有重复的邮箱地址。

解法一:(self join)

# Write your MySQL query statement below
select distinct a.Email from Person a, Person b where a.Email=b.Email and a.Id<>b.Id

一开始,写的时候没注意把distinct给漏了,导致出错:

Submission Result: Wrong AnswerMore Details

Input:{"headers": {"Person": ["Id", "Email"]}, "rows": {"Person": [[1, "paris@hilton.com"], [2, "paris@hilton.com"]]}}
Output:{"headers": ["Email"], "values": [["paris@hilton.com"], ["paris@hilton.com"]]}
Expected:{"headers": ["Email"], "values": [["paris@hilton.com"]]} 

解法二:

# Write your MySQL query statement below
Select Email
From Person
GROUP BY Email
Having count(Email)>1

二、Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe |
+----------+
分析:题意为雇员表记录了所有雇员的信息,包括他们的经理在内。每一个雇员都有一个Id,和他的经理的Id。
给定雇员表,编写一个SQL查询找出薪水大于经理的员工姓名。对于上表来说,Joe是唯一收入大于经理的员工。 使用自连接:
# Write your MySQL query statement below
select m.Name from Employee m,Employee n where m.ManagerId=n.Id and m.Salary>n.Salary;

 或:

Select emp.Name from
Employee emp inner join Employee manager
on emp.ManagerId = manager.Id
where emp.Salary > manager.Salary

 或: 

select a.name from Employee a left join Employee b on a.managerid=b.id where a.salary>b.salary

 

三、Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

分析:题意为  从员工表中找到工资第二高的数据(即比最高工资少的里面工资最高的)

代码如下:

# Write your MySQL query statement below
select max(Salary) from Employee
where Salary < (select max(Salary) from Employee);

其他解法:

# Write your MySQL query statement below
SELECT Salary FROM Employee GROUP BY Salary
UNION ALL (SELECT null AS Salary)
ORDER BY Salary DESC LIMIT 1 OFFSET 1

 或:

select (select distinct Salary from Employee order by salary desc limit 1,1) as Salary;

注:LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1);offset偏移量 

 

四、Combine Two Tables

able: Person

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

分析:题意为
有两个数据表:Person表和Address表。Person(人员)表主键为PersonId,Address(地址)表主键是AddressId,通过PersonId与Person表关联。编写一个SQL查询,对于Person表中的每一个人,取出FirstName, LastName, City, State属性,无论其地址信息是否存在。
思路:Person表是主表,Address表是从表,通过Left Outer Join左外连接即可。
# Write your MySQL query statement below
select p.FirstName,p.LastName,a.City,a.State
from Person p left outer join Address a using (PersonId); (using()用于两张表的join查询,要求using()指定的列在两个表中均存在,并使用之用于join的条件。)

  等价于

# Write your MySQL query statement below
select p.FirstName,p.LastName,a.City,a.State
from Person p left outer join Address a on p.PersonId=a.PersonId

  其他解法:

SELECT FirstName, LastName, City, State FROM Person NATURAL LEFT JOIN Address;

  

 

 

  

上一篇:Java代码执行顺序(静态变量,非静态变量,静态代码块,代码块,构造函数)加载顺序


下一篇:(转)基于OpenStack构建企业私有云(1)实验环境准备