196. 删除重复的电子邮箱
错误写法
delete from Person
where Id not in (
select min(Id) from Person
group by Email)
错误提示
不允许同时查找和删除同一张表
正确写法
运用子查询
delete from Person
where Id not in (
select Id
from(
select min(Id) Id from Person
group by Email
) t
)
627. 交换工资
update salary set sex=if(sex='f','m','f')
1083. 销售分析三
注意:in,not in 在表中的具体范围
分为两部分来考虑,1.买S8的买家,2.在1的基础上,除去买iphone的买家
select distinct buyer_id
from Sales s
join Product p
on p.product_id=s.product_id
where product_name in ('S8') and buyer_id not in (
select buyer_id
from Sales s
join Product p
on p.product_id=s.product_id
where product_name in ('iPhone')
)
1179.重新格式化部门表
select id,
sum(case month when 'Jan' then revenue else null end) Jan_Revenue,
sum(case month when 'Feb' then revenue else null end) Feb_Revenue,
sum(case month when 'Mar' then revenue else null end) Mar_Revenue,
sum(case month when 'Apr' then revenue else null end) Apr_Revenue,
sum(case month when 'May' then revenue else null end) May_Revenue,
sum(case month when 'Jun' then revenue else null end) Jun_Revenue,
sum(case month when 'Jul' then revenue else null end) Jul_Revenue,
sum(case month when 'Aug' then revenue else null end) Aug_Revenue,
sum(case month when 'Sep' then revenue else null end) Sep_Revenue,
sum(case month when 'Oct' then revenue else null end) Oct_Revenue,
sum(case month when 'Nov' then revenue else null end) Nov_Revenue,
sum(case month when 'Dec' then revenue else null end) Dec_Revenue
from Department group by id
1211.查询结果的质量和占比
select query_name,round(sum(rating/position)/count(*),2) quality,round(sum(rating<3)*100/count(*),2) oor_query_percentage
from Queries group by query_name
1280.学生们参加各科测试的次数
1.看结果前三列,每一个student_name 把所有的subject_name都考虑进去,所以考虑笛卡尔乘积,即交叉连接
2.最后一列,可以考虑,在1的基础上左连接Examinations,测试数为null(0)
select a.student_id,student_name,c.subject_name,count( b.subject_name) attended_exams
from Students a cross join Subjects c
left join Examinations b
on a.student_id=b.student_id and c.subject_name=b.subject_name
group by a.student_id,c.subject_name
order by a.student_id
1294.不同国家的天气类型
1.先计算每个国家的月平均weather_state
2.在表1的基础上,利用case,输出天气类型
select country_name,(
case
when weather_state<=15 then 'Cold'
when weather_state<25 then 'Warm'
else 'Hot' end) weather_type
from (select country_name,sum(weather_state)/count(*) weather_state
from Countries c join
Weather w
on c.country_id=w.country_id
where day between '2019-11-01' and '2019-11-30'
group by c.country_id) t
1435.创建一个分组表格
利用联合
(select '[0-5>' bin, count(*) from Sessions where duration/60<5)
union
(select '[5-10>' bin,count(*) total
from Sessions
where duration/60 >=5 and duration/60 <10 )
union
(select '[10-15>' bin,count(*) total
from Sessions
where duration/60 >=10 and duration/60 <15 )
union
(select '15 or more' bin,count(*) total
from Sessions
where duration/60 >=15 )
1485.按日期分组
select sell_date,count(distinct product) num_sold,group_concat(distinct product order by product) products
from Activities
group by sell_date
order by sell_date
concat:将多个字符串连接成一个字符串
group_concat:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )