# Write your MySQL query statement below select ifnull( (select Salary from Employee group by Salary order by Salary desc limit 1,1),null) SecondHighestSalary
select distinct l1.Num ConsecutiveNums from Logs l1 left join Logs l2 on l1.Id = l2.Id - 1 left join Logs l3 on l1.Id = l3.Id - 2 where l1.Num = l2.Num and l2.Num = l3.Num;
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
1 2 3 4 5 6 7 8 9
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
1 2 3 4 5 6 7
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
1 2 3 4 5 6 7
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
解题
1 2
select d.Name Department,e.Name Employee, e.Salary from Department d inner join Employee e on d.Id = e.DepartmentId and e.Salary = (select max(Salary) from Employee where DepartmentId = d.Id)
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
1 2 3 4 5 6 7 8 9 10 11
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
1 2 3 4 5 6 7
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
1 2 3 4 5 6 7 8 9 10
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+
解题
1 2 3 4 5 6 7 8 9
SELECT D1.Name Department,E1.Name Employee,E1.Salary FROM Employee E1, Employee E2, Department D1 WHERE E1.DepartmentID = E2.DepartmentID AND E2.Salary >= E1.Salary AND E1.DepartmentID = D1.ID GROUP BY E1.Name HAVING COUNT(DISTINCT E2.Salary) <= 3 ORDER BY D1.Name, E1.Salary DESC;
select t.Request_at Day,ROUND(sum((case when t.Status like 'cancelled%' then 1 else 0 end))/count(*),2) as'Cancellation Rate' from Trips t inner join Users u on u.Users_Id =t.Client_Id and u.Banned = 'No' where t.Request_at between '2013-10-01'and'2013-10-03' group by t.Request_at;
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
应该输出:
1 2 3 4 5 6
+---------+ | class | +---------+ | Math | +---------+
Note:
学生在每个课中不应被重复计算。
题解
1 2
select class from courses group by class having count(DISTINCT student) >= 5
select distinct s1.* from stadium s1, stadium s2, stadium s3 where s1.people >= 100 and s2.people>= 100 and s3.people >= 100 and ( (s1.id - s2.id = 1 and s2.id - s3.id =1) or (s2.id - s1.id = 1 and s1.id - s3.id =1) or (s3.id - s2.id = 1 and s2.id - s1.id = 1) ) order by s1.id;
作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
1 2 3 4 5 6 7 8 9 10
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+
对于上面的例子，则正确的输出是为：
1 2 3 4 5 6 7
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
题解
1 2
select * from cinema where description!="boring" and id%2=1 order by rating desc
select * from ( select s1.id, s2.student from seat s1,seat s2 where s1.id%2=0 and s1.id-1=s2.id union select s1.id, s2.student from seat s1,seat s2 where s1.id%2=1 and s1.id=s2.id-1 union select id, student from seat s1 where id%2=1 and id=(select id from seat order by id desc limit 1) ) as t order by t.id
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+
shell script select id , sum(case `month` when 'Jan' then revenue else null end) as Jan_Revenue , sum(case `month` when 'Feb' then revenue else null end) as Feb_Revenue , sum(case `month` when 'Mar' then revenue else null end) as Mar_Revenue , sum(case `month` when 'Apr' then revenue else null end) as Apr_Revenue , sum(case `month` when 'May' then revenue else null end) as May_Revenue , sum(case `month` when 'Jun' then revenue else null end) as Jun_Revenue , sum(case `month` when 'Jul' then revenue else null end) as Jul_Revenue , sum(case `month` when 'Aug' then revenue else null end) as Aug_Revenue , sum(case `month` when 'Sep' then revenue else null end) as Sep_Revenue , sum(case `month` when 'Oct' then revenue else null end) as Oct_Revenue , sum(case `month` when 'Nov' then revenue else null end) as Nov_Revenue , sum(case `month` when 'Dec' then revenue else null end) as Dec_Revenue from Department group by id
