1350. 院系无效的学生 1355. 活动参与者 1369. 获取最近第二次的活动 1378. 使用唯一标识码替换员工ID1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

【一表分组排名用 group by】
【多表分组排名再over内部使用partition by分组】

sum()括号里面是条件的话返回的值是满足该条件的行数，count里面貌似不可以写条件
having sum(product_name=‘A’)>0 and sum(product_name=‘B’)>0 and sum(product_name=‘C’)=0

---

1350. 院系无效的学生

---

# Write your MySQL query statement below
select id,name
from Students
where department_id not in(
    select id from Departments
)

---

1355. 活动参与者

---

# Write your MySQL query statement below
select activity
from(
select activity ,
dense_rank()over(order by count(*) desc)jiangxu,
dense_rank()over( order by count(*) asc)shengxu
from Friends
group by activity)new_table
where jiangxu <> 1 and shengxu <> 1

---

1369. 获取最近第二次的活动

---

# Write your MySQL query statement below
select username, activity, startDate, endDate
from(
select *,
dense_rank()over(partition by username order by startDate desc) paiming ,
#对有多行的进行排序（单行只有1个不能靠这个区分）
count(*)over(partition by username) geshu 
#对名字进行分组并记录个数，只有一个的就是上方不能区分的
from UserActivity
)new_table
where paiming = 2 or geshu = 1
order by username desc

---

1378. 使用唯一标识码替换员工ID

---

# Write your MySQL query statement below
select Eu.unique_id  unique_id,E.name
from EmployeeUNI Eu right join Employees E
on Eu.id = E.id

---

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

---

# Write your MySQL query statement below

select O.customer_id, customer_name
from  Orders O ,Customers C
WHERE O.customer_id=C.customer_id
group by O.customer_id
#having count(product_name='A')>0 and count(product_name='B')>0 and count(product_name='C')=0
# sum()括号里面是条件的话返回的值是满足该条件的行数，count里面貌似不可以写条件
having sum(product_name='A')>0 and sum(product_name='B')>0 and sum(product_name='C')=0