sql连接和groupBy

基本sql语句

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

统计每一个城市员工的数量

select city, count(*) as num
from staff
group by city;

筛选出年龄大于19岁的员工，并按城市分组，统计每个城市的员工数量，最后按员工数量升序排列

SELECT city,count(*) as num 
FROM staff
GROUP BY city  
ORDER BY num ASC

查询每个城市的员工数量，获取到员工数量不低于10的城市

select city,count(*) as num
from staff
group by city
having num > 10
ORDER BY num asc ;

筛选出年龄大于19岁的员工，并按城市分组，统计每个城市的员工数量，选出员工数量大于3的城市

SELECT city, COUNT(*) as num 
from staff
WHERE age > 19
GROUP BY city
HAVING num > 3;

175. 组合两个表 - 力扣（LeetCode）

select firstName, lastName, city, state    
from Person left join Address
on Person.personId = Address.personId;
#这里得使用on使用whewe会报错

181. 超过经理收入的员工 - 力扣（LeetCode）

Select name as Employee
From Employee a 
where managerId IS NOT NULL and salary >(
 Select salary
 From Employee b
 where a.managerId = b.id
);

182. 查找重复的电子邮箱 - 力扣（LeetCode）

select email as Email
from Person
group by email
having count(email) >= 2;

183. 从不订购的客户 - 力扣（LeetCode）

select Customers.name as Customers 
from Customers 
where id not in(
    select customerId 
    from Orders 
);

select Customers.name as Customers 
from Customers left join Orders 
on Customers.id = Orders.customerId
where customerId IS NULL;

197. 上升的温度 - 力扣（LeetCode）

select a.id
from Weather as a, Weather as b
where  datediff(a.recordDate, b.recordDate) = 1 and a.Temperature > b.Temperature;

这道题目这样写其实还是有一些难度的

select a.id
from Weather a inner join Weather b
where datediff(a.recordDate, b.recordDate) = 1 and a.Temperature > b.Temperature;

1050. 合作过至少三次的演员和导演 - 力扣（LeetCode）

select actor_id, director_id 
from ActorDirector 
group by actor_id, director_id 
having count(timestamp) >2;

1068. 产品销售分析 I - 力扣（LeetCode）

select Product.product_name, Sales.year, Sales.price
from Sales left join Product
on Sales.product_id  = Product.product_id;

511. 游戏玩法分析 I - 力扣（LeetCode）

select player_id, min(event_date) as first_login 
from Activity 
group by player_id;

196. 删除重复的电子邮箱 - 力扣（LeetCode）

Delete p1
from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id;