20170314LeetcodeMysql练习02

175. Combine Two Tables

Table: 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

My solution:

SELECT Person.FirstName, Person.LastName, Address.City, Address.State 
FROM Person
LEFT JOIN Address
ON Person.PersonID = Address.PersonID

 

182. 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 |
+---------+

My solutions:

01.
SELECT Email from Person
GROUP BY Email
Having count(*) > 1

02.
SELECT DISTINCT p1.Email FROM Person p1
JOIN Person p2
ON p1.Email = p2.Email
WHERE p1.Id <> p2.Id

 

posted @ 2017-03-14 20:02  sheepshaker  阅读(110)  评论(0)  编辑  收藏  举报