1.删除 menu 表中所有重复的邮件,重复的邮件里只保留 id 最大的那个。
delete from menu
where id not in
(select max(id) as id from person group by email)
//error:你不能依据条件更新表
**You can't specify target table 'menu ' for update in FROM clause**
解决方案:
delete from menu
where id not in
(select id from
(select max(id) as id from person group by email)
)
//该方案引发了新的问题
//子查询必须具有自己的别名
**error:Every derived table must have its own alias**
解决方案:
delete from menu
where id not in
(select id from
(select min(id) as id from person group by email) as p
)
等值连接:
select * from A as a inner join A as b on a.xx=b.xx
.
也可:select * from A as a , A as b where a.xx=b.xx
自然连接-连接的是自身
左外连接-全看左表,若左表有值,而右表无值,则结果集右侧部分置空显示,若左表无值,不管右表有没有值,都不显示。
右外连接-全看右表,与左外连接相反
select num from logs as a,logs as b
where a.id=b.id and a.num=b.num
//error:字段列表中的"num"列模棱两可
Column 'num' in field list is ambiguous
解决方案:
select a.num from logs as a,logs as b
where a.id=b.id and a.num=b.num
交换属性值
1、update user set gender=if(gender=0,1,0)
·
2、update user set gender=case when gender=1 then 0 else 1 end where gender in (0, 1)
·
3、update user set gender=CHAR(ASCII(gender) ^ ASCII('1') ^ ASCII('0'))
查询报名人数大于等于5人的选修课,过滤重复报名的人(重复报名,人数只算一人)
select class from (select distinct student,class from courses) as c
group by class
having count(class) >= 5
力扣版:
SELECT class FROM courses
GROUP BY class
HAVING count( DISTINCT student ) >= 5
作者:CyC2018
链接:https://leetcode-cn.com/leetbook/read/tech-interview-cookbook/oaq8n5/
来源:力扣(LeetCode)
distinct 去除重复行,注意是行,因此行与行若有一个字段不重复,则就不算重复。
为了在没有查找到数据时返回 null,需要在查询结果外面再套一层 SELECT。
SELECT (SELECT DISTINCT NAME FROM USER);