der, return null
MySQL IFNULL()函数用法
IFNULL(expr1,expr2)
如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。
mysql delimiter
告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号。
在定义Function 时，将delimiter 改为 $$ 或者其它可唯一作为界定的符号。否则函数定义未录入完成前，mysql遇到分号就判定函数结束并开始执行，这样会导致错误。
delete function
DROP FUNCTION getNthHighestSalary;
call function
SELECT getNthHighestSalary(8);
mysql LIMIT
select * from table limit m,n
其中m是记录开始的index，0表示第一条记录，从第m+1条开始取。n是指取n条记录。select * from tablename limit 2,4即取出第3条至第6条，4条记录。
mySQL中实现rownum.的功能
| select @rownum := @rownum+1 as rn, temp.sal ss from ( select distinct e.salary sal from Employee e order by e.salary DESC ) temp , (select @rownum :=0) r ； |
输出为（‘rn’ is the rownum ）
mySQL 版本的查询
| CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( select ifnull(ttm.salary,null) salary from ( select @row_num := @row_num+1 rn , temp.sal salary from ( select distinct em.salary sal from Employee em order by em.salary DESC ) temp, (SELECT @row_num := 0) r ) ttm where ttm.rn=N ); END # ttm.rn=N means get the top one |
Result
?
第九题
Delete Duplicate Emails
Write a SQL query to delete all duplicate email entries in a table namedPerson, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should havethe following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
建表及插入测试数据
drop table person purge; create table person(id int,email char(50)); insert into person(id,email) values(1,'jo@ex.com'); insert into person(id,email) values(2,'bob@ex.com'); insert into person(id,email) values(3,'jo@ex.com'); |
oracle 版本的查询 (works for mysql)
| select a.id,a.email from Person a, Person b where ( a.email=b.email and a.id
union select c.id,c.email from Person c where ( select count(1) from Person d where c.email=d.email )=1; |
oracle 版本的查询 (works for mysql) – not pass yet
| select min(a.id) id, a.email from Person a group by a.email ; |
?
2015.8.19
https://leetcode.com
第十题
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 |
+---------+
建表及插入测试数据
与自身做连接查询，找出那些email相等但id不相等的行。
oracle 版本的查询 (works for mysql)
select distinct a.email from Person a, Person b where ( a.email=b.email and a.id
|
?
第十一题
TheTrips table holds all taxi trips. Each trip has a unique Id, while Client_Idand Driver_Id are both foreign keys to the Users_Id at the Users table. Statusis an ENUM type of (‘completed’, ‘cancelled_by_driver’,‘cancelled_by_client’). -
?
+----+-----------+-----------+---------+--------------------+----------+
|Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
|1 | 1 | 10 | 1 | completed |2013-10-01|
|2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
|3 | 3 | 12 | 6 | completed |2013-10-01|