classSolution { publicint[] twoSum(int[] nums, int target) { inta=0, b = 0; Map<Integer, Integer> map = newHashMap<Integer, Integer>(); for (inti=0; i < nums.length; i++) { intcomplement= target - nums[i]; if (map.containsKey(complement)) { a = i; b = map.get(complement); } else { map.put(nums[i], i); } } returnnewint[]{a, b}; } }
Submission Detail
29 / 29 test cases passed.
Runtime: 2 ms, faster than 72.04% of Java online submissions for Two Sum.
Memory Usage: 39.2 MB, less than 98.23% of Java online submissions for Two Sum.
Database
175. Combine Two Tables
mysql
1 2 3
select p.FirstName FirstName,p.LastName LastName,a.City City, a.State State from Person p leftouterjoin Address a on p.PersonId=a.PersonId
Submission Detail
7 / 7 test cases passed.
Your runtime beats 84.70 % of mysql submissions.
Transact-SQL
1 2 3
SELECT P.firstName AS'firstName', P.lastName AS'lastName', A.city AS'city', A.state AS'state' FROM Person AS P LEFTOUTERJOIN Address AS A ON A.personId = P.personId
Submission Detail
8 / 8 test cases passed.
Your runtime beats 44.40 % of mssql submissions.
176. Second Highest Salary
mysql
1
1 2
select IF(count(distinct Salary)>1,min(t.Salary),null) SecondHighestSalary from (select Salary from Employee orderby Salary desc limit 2) t
Submission Detail
7 / 7 test cases passed.
Your runtime beats 90.90 % of mysql submissions.
2
1 2 3 4
select ( selectdistinct Salary from Employee orderby Salary desc limit 1,1 ) AS SecondHighestSalary
Submission Detail
7 / 7 test cases passed.
Your runtime beats 88.73 % of mysql submissions.
Transact-SQL
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT CASE WHENCOUNT(V.salary) >1THENMIN(V.salary) ELSENULL ENDAS'SecondHighestSalary'FROM ( SELECT TOP 2 SA.salary FROM ( SELECTDISTINCT EM.salary FROM Employee AS EM ) AS SA ORDERBY SA.salary DESC ) AS V
Submission Detail
8 / 8 test cases passed.
Runtime: 603 ms, faster than 85.83% of MS SQL Server online submissions for Second Highest Salary.
Memory Usage: 0B, less than 100.00% of MS SQL Server online submissions for Second Highest Salary.
177. Nth Highest Salary
1 2 3 4 5 6
CREATEFUNCTION getNthHighestSalary(N INT) RETURNSINT BEGIN DECLARE fromRow INT; SET fromRow = N-1; RETURN (select (selectdistinct Salary from Employee orderby Salary desc limit 1offset fromRow)); END
Submission Detail
14 / 14 test cases passed.
Your runtime beats 86.66 % of mysql submissions.
178. Rank Scores
1 2 3 4 5 6 7 8 9
select a.Score,b.`Rank` from Scores a, ( select t.Score,(@row_number:=@row_number+1) as `Rank` from ( selectdistinct Score from Scores orderby Score desc ) t,(SELECT@row_number:=0) t2 ) b where a.Score=b.Score orderby a.Score desc
Submission Detail
10 / 10 test cases passed.
Your runtime beats 98.24 % of mysql submissions.
181. Employees Earning More Than Their Managers
1
select t.Name as Employee from Employee t where t.Salary>(select e.Salary from Employee e where e.Id=t.ManagerId)
Submission Detail
14 / 14 test cases passed.
Your runtime beats 39.24 % of mysql submissions.
182. Duplicate Emails
1 2
select t.Email as Email from (select Email,count(Email) from Person groupby Email havingcount(Email)>1) t
Submission Detail
15 / 15 test cases passed.
Your runtime beats 76.83 % of mysql submissions.
183. Customers Who Never Order
1
1 2
select c.Name as Customers from Customers c where c.Id notin (selectdistinct o.CustomerId from Orders o)
Submission Detail
12 / 12 test cases passed.
Your runtime beats 84.65 % of mysql submissions.
2
1 2 3
select c.Name as Customers from Customers c leftouterjoin Orders o on c.Id=o.CustomerId where o.Id isnull
delete p from Person as p, Person as t where p.Id>t.Id and p.Email=t.Email;
Submission Detail
22 / 22 test cases passed.
Your runtime beats 18.50 % of mysql submissions.
2
1
delete p from Person as p, Person as t where p.Email=t.Email and p.Id>t.Id;
Submission Detail
22 / 22 test cases passed.
Your runtime beats 83.82 % of mysql submissions.
3
1
delete p from Person as p innerjoin Person as t where p.Email=t.Email and p.Id>t.Id;
Submission Detail
22 / 22 test cases passed.
Your runtime beats 80.14 % of mysql submissions.
180. Consecutive Numbers
被卡了好几天，百思不得其解，又开始怀疑是不是select还不足以，难道要用到游标与临时表之类，再琢磨下去也是浪费时间，遇到什么问题，就去问google，尤其是这种自己不会别人会的技术问题，搜 mysql consecutive queries 定位到 MySQL query for 3 consecutive integers between records，冥思苦想就是想不对路，一看到人家的解就恍然大悟，自己怎么会想不到这一点，看来自己的sql还是需要外部启发，没有点拨，自己就迷失在思维定势里了。
不过这题有点让人犯糊涂，如果id不连续呢？那是不是还得用计算列row_num?反正题意是id连续
1 2 3
selectdistinct t.Num as ConsecutiveNums from Logs t whereexists (select1from Logs x where x.Id-1=t.Id and x.Num=t.Num) and exists(select1from Logs x where x.Id+1=t.Id and x.Num=t.Num);
select d.Name as Department,e.Name as Employee,b.Salary as Salary from ( select a.DepartmentId,a.Salary from ( select t.DepartmentId,t.Salary,(@num:=if(@deptId= t.DepartmentId, @num+1, if(@deptId := t.DepartmentId, 1, 1))) as row_number from ( selectdistinct DepartmentId,Salary from Employee ) t CROSSJOIN (select@num:=0, @deptId:=null) c orderby t.DepartmentId,t.Salary desc ) a where a.row_number<=3 ) b leftouterjoin Employee e using(DepartmentId,Salary) innerjoin Department d on e.DepartmentId=d.Id orderby b.DepartmentId,b.Salary desc;
select d.Name as Department,e.Name as Employee,t.Salary from Department d leftouterjoin (select DepartmentId,Max(Salary) as Salary from Employee groupby DepartmentId) t on t.DepartmentId=d.Id innerjoin Employee e on t.Salary=e.Salary and t.DepartmentId=e.DepartmentId orderby t.Salary desc;
Submission Detail
15 / 15 test cases passed.
Your runtime beats 77.87 % of mysql submissions.
2
1 2 3 4
select d.Name as Department,e.Name as Employee,t.Salary from Department d leftouterjoin (select DepartmentId,Max(Salary) as Salary from Employee groupby DepartmentId) t on d.Id=t.DepartmentId innerjoin Employee e on e.DepartmentId=t.DepartmentId and e.Salary=t.Salary orderby t.Salary desc;
Submission Detail
15 / 15 test cases passed.
Your runtime beats 87.87 % of mysql submissions.
3
1 2 3 4
select d.Name as Department,e.Name as Employee,t.Salary from Department d leftouterjoin (select DepartmentId,Max(Salary) as Salary from Employee groupby DepartmentId) t on d.Id=t.DepartmentId innerjoin Employee e using(DepartmentId,Salary) orderby t.Salary desc;
select b.Request_at asDay,round(IFNull(a.cancel_num,0)/b.total_num,2) as `Cancellation Rate` from ( select t.Request_at,count(*) as cancel_num from Trips t where t.Client_Id in (select Users_Id from Users where Role='client'and Banned='No') and t.Driver_Id in (select Users_Id from Users where Role='driver'and Banned='No') and t.Status in ('cancelled_by_driver','cancelled_by_client') and t.Request_at betweenDATE'2013-10-01'andDATE'2013-10-03' groupby t.Request_at ) a rightouterjoin ( select t.Request_at,count(*) as total_num from Trips t where t.Client_Id in (select Users_Id from Users where Role='client'and Banned='No') and t.Driver_Id in (select Users_Id from Users where Role='driver'and Banned='No') and t.Request_at betweenDATE'2013-10-01'andDATE'2013-10-03' groupby t.Request_at ) b on b.Request_at=a.Request_at;
Submission Detail
9 / 9 test cases passed.
Your runtime beats 82.92 % of mysql submissions.
2
1 2 3 4 5 6 7 8 9 10 11 12 13 14
select a.Request_at asDay,round(IFNull(b.cancel_num,0)/a.total_num,2) as `Cancellation Rate` from ( select t.Request_at,count(*) as total_num from Trips t where t.Client_Id in (select Users_Id from Users where Role='client'and Banned='No') and t.Driver_Id in (select Users_Id from Users where Role='driver'and Banned='No') and t.Request_at betweenDATE'2013-10-01'andDATE'2013-10-03' groupby t.Request_at ) a leftouterjoin ( select t.Request_at,count(*) as cancel_num from Trips t where t.Client_Id in (select Users_Id from Users where Role='client'and Banned='No') and t.Driver_Id in (select Users_Id from Users where Role='driver'and Banned='No') and t.Status in ('cancelled_by_driver','cancelled_by_client') and t.Request_at betweenDATE'2013-10-01'andDATE'2013-10-03' groupby t.Request_at ) b on b.Request_at=a.Request_at;
Submission Detail
9 / 9 test cases passed.
Your runtime beats 61.34 % of mysql submissions.
595. Big Countries
这题确实Easy，or条件或用据说性能更好的 union (Set operations)
1
1
select name,population,area from World where area>3000000or population>25000000;
Submission Detail
4 / 4 test cases passed.
2
1 2 3
select name,population,area from World where area>3000000 union select name,population,area from World where population>25000000;
Submission Detail
4 / 4 test cases passed.
596. Classes More Than 5 Students
这题也Easy，group by + having子句
1 2 3 4
select t.`class` as `class` from ( select `class`,count(distinct student) as n from courses groupby `class` having n>=5 ) t;
Submission Detail
9 / 9 test cases passed.
601. Human Traffic of Stadium
这题又被卡了好几天，google也没搜到什么直接的线索，PL/SQL与T-SQL有分析函数ROW_NUMBER(), RANK(), and DENSE_RANK()，还以为需要mysql模拟这种功能。
卡在这一步里
select s.* from stadium s where s.people>=100
and exists(select 1 from stadium t2 where t2.people>=100 and t2.`date`=DATE_SUB(s.`date`,INTERVAL 1 DAY))
and exists(select 1 from stadium t3 where t3.people>=100 and t3.`date`=DATE_ADD(s.`date`,INTERVAL 1 DAY));
select x.*from ( select a.*from (select s.*from stadium s where s.people>=100) a, ( select s.*from stadium s where s.people>=100 andexists(select1from stadium t2 where t2.people>=100and t2.id=s.id-1) andexists(select1from stadium t3 where t3.people>=100and t3.id=s.id+1) ) b where a.id=b.id-1
union
select s.*from stadium s where s.people>=100 andexists(select1from stadium t2 where t2.people>=100and t2.id=s.id-1) andexists(select1from stadium t3 where t3.people>=100and t3.id=s.id+1)
union
select c.*from ( select s.*from stadium s where s.people>=100 andexists(select1from stadium t2 where t2.people>=100and t2.id=s.id-1) andexists(select1from stadium t3 where t3.people>=100and t3.id=s.id+1) ) b, (select s.*from stadium s where s.people>=100) c where c.id=b.id+1 ) x orderby x.id;
select*from ( select t1.id,t2.student from (select*from seat where id%2=1) t1 join (select*from seat where id%2=0) t2 on t1.id=t2.id-1
union
select IFNULL(t2.id,t1.id) as id,t1.student from (select*from seat where id%2=1) t1 leftouterjoin (select*from seat where id%2=0) t2 on t1.id=t2.id-1 ) t orderby id;
Submission Detail
12 / 12 test cases passed.
Your runtime beats 58.11 % of mysql submissions.
627. Swap Salary
算是Easy吧，不过还是看了下mysql的update语法，用IF()函数或者 case 都可以。
1
1
update salary set sex=IF(sex='m','f','m');
Submission Detail
8 / 8 test cases passed.
Your runtime beats 53.28 % of mysql submissions.
2
1
update salary set sex=case sex when'm'then'f'when'f'then'm'end;
LIMIT row_count is equivalent to LIMIT 0, row_count
LIMIT {[offset,] row_count
limit row_count offset offset
For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)
GROUP_CONCAT
This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:
select a.Score,b.`Rank` from Scores a,
(
select t.Score,(@row_number:=@row_number+1) as `Rank` from
(
select distinct Score from Scores order by Score desc
) t,(SELECT @row_number:=0) t2
) b
where a.Score=b.Score
order by a.Score desc;
185. Department Top Three Salaries
select t.DepartmentId,t.Salary,(@num:=if(@deptId = t.DepartmentId, @num +1, if(@deptId := t.DepartmentId, 1, 1))) as row_number from
(
select distinct DepartmentId,Salary from Employee
) t
CROSS JOIN (select @num:=0, @deptId:=null) c
order by t.DepartmentId,t.Salary desc;
JOIN
INNER JOIN vs. CROSS JOIN
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
In MySQL, For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
LEFT JOIN vs. RIGHT JOIN
RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.
User-Defined Variables
You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another. User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically freed when that client exits.
User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @’my-var’, @”my-var”, or @`my-var`).
User variable names are not case sensitive.
One way to set a user-defined variable is by issuing a SET statement:
SET @var_name = expr [, @var_name = expr] …
For SET, either = or := can be used as the assignment operator.
You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:
SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.
Multi-Table Deletes
You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause.
For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.
If you declare an alias for a table, you must use the alias when referring to the table: DELETE t1 FROM test AS t1, test2 WHERE ...
Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. Elsewhere, alias references are permitted but not alias declarations.
Correct:
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
Incorrect:
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;
DISTINCT
The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.
Jim Gray Summary Home Page http://jimgray.azurewebsites.net/ His primary research interests are in databases and transaction processing systems – with particular focus on using computers to make scientists more productive.
Bud Wilkinson, former football coach at the University of Oklahoma, was in Dallas for a series of lectures on physical fitness. A TV reporter interviewed him about the President’s physical fitness program and asked: “Mr Wilkinson, what would you say is the contribution of modern football to physical fitness?” The reporter expected a lengthy speech.
As if he had been waiting 30 years for this question, he said, “Absolutely nothing.”
The young reporter stared and squirmed and finally stuttered, “Would you care to elaborate on that?”
Wilkinson said, “Certainly. I define football as 22 men on the field who desperately need rest and 50,000 people in the stands who desperately need exercise.”