两表联合
组合两个不同的表
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 | |
---|---|
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