MySQL查询表里某个字段重复次数最多的语句以及一道面试题

莫物

已于 2022-09-19 14:36:10 修改

阅读量561

点赞数

文章标签： mysql 数据库 sql

于 2022-09-19 14:32:50 首次发布

本文链接：https://blog.csdn.net/weixin_45618937/article/details/126933120

版权

按这个重复字段进行分组再根据数量排序

select name,count(*) from table group by name having count(*)>1 order by count(*) desc limit 0,10;

面试题

在这里插入图片描述

（1）select count(*),c.cname from SC sc 
							left join Student s on s.sid = sc.sid 
							left join Course c on c.cid = sc.cid group by c.cid;
（2）select s.sid,s.sname,sc.score from SC sc 
							left join Student s on s.sid = sc.sid 
							left join Course c on c.cid = sc.cid 
							where c.cname = "计算机" and sc.score > 60;
（3）delete from SC where sid in 
					(select sid from 
						(select sid from Course c 
							left join SC s on c.cid = s.cid 
							left join Teacher t on t.tid = c.tid 
							where t.tname = "David") tt);