ps:百度题目,可以看到完整描述
175.
Combine Two Tables
select
p.FirstName, p.LastName, a.City, a.State
from
Person p
left
outer
join
Address a
on
p.PersonId=a.PersonId;
176.
Second Highest Salary
select
IFNULL( (
select
e.Salary
from
Employee e
group
by
e.Salary
order
by
e.Salary
desc
limit 1, 1),
NULL
) SecondHighestSalary;
177.
Nth Highest Salary
CREATE
FUNCTION
getNthHighestSalary(N
INT
)
RETURNS
INT
BEGIN
RETURN
(
select
IFNULL(Salary,
NULL
) Salary
from
(
select
@row_num := @row_num+1 Rank, Salary
from
(
select
Salary
from
Employee
group
by
Salary
desc
) t1
join
(
select
@row_num := 0
from
dual
) t2
) t
where
t.Rank=N
);
END
180.
Consecutive Numbers
select
DISTINCT
(l1.Num)
from
Logs l1, Logs l2, Logs l3
where
l1.Id+1=l2.Id
and
l1.Id+2=l3.Id
and
l1.Num=l2.Num
and
l1.Num=l3.Num;
181.
Employees Earning More Than Their Managers
select
e.
Name
from
Employee e, Employee m
where
e.ManagerId=m.Id
and
e.Salary>m.Salary;
182.
Duplicate Emails
select
distinct
(p.Email)
from
Person p, Person q
where
p.Id!=q.Id
and
p.Email=q.Email;
183.
Customers Who Never Order
select
c.
Name
Customers
from
Customers c
where
c.Id
not
in
(
select
CustomerId
from
Orders
)
184.
Department Highest Salary
SELECT
d.Name
AS
Department, e.Name
AS
Employee, e.Salary
FROM
Employee e, Department d
WHERE
e.DepartmentId
=
d.Id
AND
e.Salary
=
(
SELECT
MAX
(Salary)
FROM
Employee e2
WHERE
e2.DepartmentId
=
d.Id);
185.
Department Top Three Salaries
SELECT
d.Name
AS
Department, e.Name
AS
Employee, e.Salary
FROM
Employee e
JOIN
Department d
on
e.DepartmentId
=
d.Id
WHERE
(
SELECT
COUNT
(
DISTINCT
Salary)
FROM
Employee
WHERE
Salary
>
e.Salary
AND
DepartmentId
=
d.Id)
<
3
ORDER
BY
d.Name, e.Salary
DESC
;
查询某门课是最高成绩的记录
第一种:先使用group by和max得到最高分数和学科,然后再查询一下score表,找到学科和分数都相同的记录
select b.* from (select max(score) t,course from score group by course) a,score b where a.t=b.score and a.course=b.course
第二种:先得到相同学科的最高分数,再查询score表,找到最高分数的记录
select * from score a where score=(select max(score) from score where course=a.course)