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
重名大师: 真的很有用,弄完qt界面学习再看这个就成功了
Hallowang77: 他的代码要改下 分组需要用 D.dept_name 其他的都没问题 select dept_name , COUNT(student_id) student_number from Student S right join Department D on S.dept_id=D.dept_id group by D.dept_name order by student_number desc ,d.dept_name;
CSDN-Ada助手: 一定要坚持创作更多高质量博客哦, 小小红包, 以资鼓励, 更多创作活动请看: 新星计划2023: https://marketing.csdn.net/p/1738cda78d47b2ebb920916aab7c3584?utm_source=csdn_ai_ada_redpacket 物联网技术正在如何影响我们的生活: https://activity.csdn.net/creatActivity?id=10421?utm_source=csdn_ai_ada_redpacket 上传ChatGPT/计算机论文等资源,瓜分¥5000元现金: https://blog.csdn.net/VIP_Assistant/article/details/130196121?utm_source=csdn_ai_ada_redpacket 全部创作活动: https://mp.csdn.net/mp_blog/manage/creative?utm_source=csdn_ai_ada_redpacket
蔡徐坤学C++: QT += core gui sql
好好读术,天天向上: 我以为蔡徐坤这个人学C++