Sql复杂语法

两表联合

组合两个不同的表

Person中PersonId与Address中PersonId相同的提取Address中的city和state，不存在相同的则赋值为null
tableB中全部显示：tableA right join tableB on …（A表靠后）
tableA中全部显示：tableA left join tableB on …（A表靠前）

select Person.firstName, Person.lastName, Address.city, Address.state from Address right join Person on Person.PersonId = Address.PersonId

select Person.firstName, Person.lastName, Address.city, Address.state from Person left join Address on Person.PersonId = Address.PersonId

组合两个相同的表

id	name	managerId
1	Joe	3
2	Henry	4
3	Sam	Null
4	Max	Null

找到员工和管理员的对应关系

SELECT a.name as ‘employee’, b.name as ‘manager’
FROM Employee AS a, Employee AS b
where(
    a.managerId = b.id
);

操作

统计某列数据相同的数量

±—±--------+

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

±—±--------+
统计Email各种相同的数量

select Email, count(Email) as num
from Person
group by Email;

统计重复

1、利用查询出来的新表
select Email from (
    select Email, count(Email) as num
    from Person group by Email
) as a where num >1

2、使用having
select Email
from Person
group by Email
having count(Email) > 1;

优先级：where>group by>having>order by