182. 查找重复的电子邮箱
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
Id | |
---|---|
1 | a@b.com |
2 | c@d.com |
3 | a@b.com |
根据以上输入,你的查询应返回以下结果:
a@b.com |
方法一
创建一个辅助表,将姓名列进行行分组汇总
select 姓名, count(姓名) as 计数
from 学生表
group by 姓名;
2)选出辅助表中计数大于1的姓名
结合前两步,将“创建辅助表”的步骤放入子查询
select 姓名 from
(
select 姓名, count(姓名) as 计数
from 学生表
group by 姓名
) as 辅助表
where 计数 > 1;
select Email from(
select Email,count(Email) as n
from Person group by Email
) as statistic where n > 1
方法二having语句
select 姓名
from 学生表
group by 姓名
having count(姓名) > 1;
select Email from Person group by Email having count(Email) > 1;
595. 大的国家
这里有张 World 表,如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。
编写一个SQL查询,输出表中所有大国家的名称、人口和面积。
select name,population,area from World where area > 3000000 or population > 25000000
627. 交换工资
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
update salary
set sex = case sex
when 'm' then 'f'
else 'm'
end
620. 有趣的电影
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。(降序)
select * from cinema
where description != 'boring' and mod(id,2) = 1
order by rating DESC
175. 组合两个表
select
FirstName,LastName,City,State
from
Person left join Address on Person.PersonId = Address.PersonId
181. 超过经理收入的员工
select
a.name as 'Employee'
from
Employee as a,
Employee as b
where
a.ManagerId = b.ID
and a.Salary > b.Salary
183. 从不订购的客户
select Customers.Name as 'Customers'
from Customers
where Customers.Id not in (
select CustomerId from Orders
)
1179. 重新格式化部门表
SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
GROUP BY id;
196. 删除重复的电子邮箱
delete p1 from Person p1,Person p2
where p1.Email = p2.Email and p1.Id > p2.Id
197. 上升的温度
select a.Id
from Weather as a cross join Weather as b
on datediff(a.RecordDate,b.RecordDate) = 1
where a.Temperature > b.Temperature
596. 超过5名学生的课
select
class
from courses
group by class
having count(distinct(student)) >= 5
176. 第二高的薪水
select
max(Salary) as SecondHighestSalary
from
Employee
where Salary < (select max(Salary) from Employee)