简单题
182. 查找重复的电子邮箱
SQL:方法一
select email from person group by email having count(email) > 1;
通过 group by 对 email 分组,在使用 having 将重复的 email 筛选出来。
SQL:方法二
select t.email from (
select email, count(email) num from person group by email
) t where t.num > 1;
将 email 分组后计算出相同 email 的数量作为临时表,筛选出 num > 1 的邮箱
SQL:方法三
with temp as (
select email, count(email) num from person group by email
)
select email from temp where num > 1;
使用 with 建立临时表,和方法二一样
SQL:方法四
select distinct person.email from person
left join person temp on person.email = temp.email
where person.id != temp.id;
使用 left join 自连,连接条件是 person.eamil = temp.email 并且通过 where 把 person.id != temp.id筛选出来,最后再通过 distinct 去重
196. 删除重复的电子邮箱
SQL:方法一
delete person from person
join person p2
on person.email = p2.email and person.id > p2.id;
自连接,连接条件是 person.email = p2.email 筛选出 person.id > p2.id ,这个是要删除的。
SQL:方法二
delete person from person, person p2
where person.email = p2.email and person.id > p2.id;
和方法一一样。
SQL:方法三
delete person from person,
(
select min(id) as id, email from person group by email having count(email) > 1) as p2
where person.email = p2.email and person.id != p2.id;
将 person 安装 email 进行分组,并用 having 筛选出重复的最小 id
将这个临时表在和 person 进行一起联查,条件是 person.email = p2.email 并且不能和临时表的 id 相同,结果就是要删除的数据。
1667、修复表中的名字
CONCAT() 函数
CONCAT 可以将多个字符串拼接在一起。LEFT(str, length) 函数
从左开始截取字符串,length 是截取的长度。UPPER(str) 与 LOWER(str)
UPPER(str) 将字符串中所有字符转为大写LOWER(str) 将字符串中所有字符转为小写
SUBSTRING(str, begin, end)
截取字符串,end 不写默认为空。SUBSTRING(name, 2) 从第二个截取到末尾,注意并不是下标,就是第二个。
select user_id,
CONCAT(
UPPER(left(name, 1)),
LOWER(RIGHT(name, length(name) - 1)))
as name
from Users
order by user_id
1484. 按日期分组销售产品
1.分组
2.排序
3.组内拼接
排序,分隔符
SELECT sell_date,
COUNT(DISTINCT product) AS 'num_sold',
GROUP_CONCAT(DISTINCT product
ORDER BY product ASC #按照字典序排列,升序
SEPARATOR ',') #用','分隔
AS 'products' #组内拼接
FROM Activities
GROUP BY sell_date
ORDER BY sell_date;
group_concat用法:
group_concat([去重:distinct] '字符串' [排序:group by 该字符串 asc/desc] 分隔符:separator ',')
理解:group_concat()函数,顾名思义与group by 有关,功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
所以最后是对sell_date分组才能达到效果,group by sell_date