| 设为首页 加入收藏 |
当前位置： |
| TOP | |||
|
LeetcodeDatabase-我的汇总(四)
第一次刷了3个sql 的题。 2015.8.14 在本地测试用的Linux + oracle， leetcode 上用的是mySQL环境。 三个题都在本地测试通过了，到mySQL 上面就各种编译不通过，折腾了好几次才被accept 不过也算是对oracle 与 mySQL 的区别有了一些了解。 ? 第一题 Weather 第二题 Customer with no order 第三题 Rank Scores 第四题 DepartmentTop Three Salaries 第五题 ConsecutiveNumbers 第六题Department HighestSalary 第七题Second highest salary 第八题Nth Highest Salary 第九题Delete DuplicateEmails 第十题Duplicate Emails 第十一题Trips and Users 第十二题Delete Duplicate Emails 第一题 Given a Weather table, write a SQL query tofind all dates' Ids with higher temperature compared to its previous(yesterday's) dates. 建表及插入测试数据 oracle 版本的查询 mySQL 版本的查询 比较 第二题 Suppose that a website contains two tables,the Customers table and the Orders table. Write a SQL query to find allcustomers who never order anything. 建表及插入测试数据 oracle 版本的查询 第三题 Rank Scores Write a SQL query to rank scores. If there is a tie between twoscores, both should have the same ranking. Note that after a tie, the nextranking number should be the next consecutive integer value. In other words,there should be no "holes" between rank. 建表及插入测试数据 oracle 版本的查询 mySQL 版本的查询 比较 Oracle 分析函数实现 2015.8.17 第四题 Department Top Three Salaries 建表及插入测试数据 假设待排名次的分数是： 99， 98，98，97 名次的两种排法： oracle 版本的查询-有间隔 (worksfor mySQL) oracle 版本的查询-无间隔-1 (worksfor mySQL) Oracle 分析函数实现 第五题 Consecutive Numbers Write a SQL query to find all numbers that appear at least three timesconsecutively. +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ For example, given the above Logs table, 1 is the only number that appearsconsecutively for at least three times. 建表及插入测试数据 oracle 版本的查询-1 needto fulfill two conditions: 1. 3 rows selected in inner query 2. all three records have same 'num' value below code fulfill condition 2, but not 1 oracle版本的查询-2 mySQL 版本的查询 (error in leetcode) 第六题 Department Highest Salary TheEmployee table holds all employees. Every employee has an Id, a salary, andthere is also a column for the department Id. | Id | Name | Salary | DepartmentId | | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 |The Department tableholds all departments of the company. +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+Write a SQL query to find employees who have thehighest salary in each of the departments. For the above tables, Max has thehighest salary in the IT department and Henry has the highest salary in theSales department. +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+建表及插入测试数据 oracle 版本的查询 (works for mySQL) mysql查询2 – 不正确的 employee name 分组查询时，select中不能出现group by及聚合函数中都没有用到的列名。 mysql查询3 第七题 Second highest salary Write aSQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+Forexample, given the above Employee table, the second highest salary is 200. Ifthere is no second highest salary, then the query should return null. 建表及插入测试数据 oracle 版本的查询 (works for mySQL) Functions like MAX, MIN, SUM will return null if the query result is“norow selected”. 第八题 Nth Highest Salary 建表及插入测试数据 知识点： if N is out of or |
| 首页 上一页 1 2 3 4 5 6 下一页 尾页 4/6/6 | |
| 【大 中 小】【打印】 【繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部】 | |
|
分享到:
|
|
| 上一篇：Oracle11gR2上遇到blockingtxnidf.. | 下一篇：Mongodb的安装和简单的使用 |
| 评论 |
|
|