数据库题解

索隆各科成绩

select b.sname, c.sid ,  c.cname,c.score 
FROM result AS c , student AS b
where c.sid = b.sid and b.sname="索隆"

打印出路飞的数学成绩（百度）

select b.sname, c.sid ,  c.cname,c.score 
FROM result AS c , student AS b
where c.sid = b.sid and b.sname="路飞"
AND c.cname="数学"

打印出1班数学前2名的学生信息（百度）

select b.sname, c.sid ,  c.cname,c.score 
FROM result AS c , student AS b
where c.sid = b.sid and b.classid=1
AND c.cname="数学"
ORDER BY c.score desc
LIMIT 2

求各个科目前3名的学生（腾讯）

SELECT b.sname, c.sid, c.cname, c.score
FROM result AS c
JOIN student AS b ON c.sid = b.sid
WHERE  (
  -- 自查训
  select count(distinct score)
  from result as r1
  where r1.courseid = c.courseid
  and score>c.score
) <3
ORDER BY c.cname, c.score DESC;

求每个班级的总分

(腾讯)

SELECT b.classid ,sum(c.score)
FROM result AS c
JOIN student AS b ON c.sid = b.sid
group by b.classid

求每门课程的最大分数（百度）

SELECT c.courseid ,max(c.score)
FROM result AS c
JOIN student AS b ON c.sid = b.sid
group by c.courseid

---

SELECT c.sid , b.sname, sum(c.score)
FROM result AS c , (
select sid , sname from student
where classid =1 and sname LIKE"路%"
) as b
where c.sid = b.sid
group by c.sid , b.sname

取总成绩最高的三位学生展示，输出：学号、总成绩（美

团)

SELECT c.sid , sum(c.score) as sc
FROM result AS c 
-- student as b
-- where c.sid = b.sid
group by c.sid 
ORDER BY sc desc
LIMIT 3

查询平均分低于80的

SELECT b.sname , b.sid  , avg(c.score) as av
FROM result AS c ,
student as b
where c.sid = b.sid
group by b.sid , b.sname
HAVING  avg(c.score) < 80

查询学生数量

SELECT b.classid , count(1) as tt
FROM result AS c ,
student as b
where c.sid = b.sid
group by b.classid

求重复姓名

-- select b.sname 
-- from student as b 
-- WHERE b.sid in (
-- 	select sid 
-- 	from student
-- 	where sname = b.sname 
-- 	and sid !=b.sid
-- )

SELECT sname,COUNT(*)as ct
FROM student
WHERE classid=1
GROUP BY sname HAVING ct>1

求最少选了4个course

select s.student_id ,s.name ,count(s.course_id) as ct
from t_student as s
group by s.student_id ,s.NAME
having ct >=4

求低于69 家伙的平均分

select name , avg(course_score) as sc 
FROM t_student 
where student_id in (
SELECT DISTINCT student_id 
from t_student
WHERE course_score < 60) 
group by name
order by sc desc

科目相同成绩学生

select a.name 
from t_student as a ,t_student as b 
where a.student_id=b.student_id and 
a.course_id !=b.course_id and a.course_score = b.course_score 
group by a.name
having count(a.name) >=2

都在80以上的

select a.student_id
from t_student as a 

group by a.student_id
having min(course_score)>=80

统计大于60的学生数量

select count(distinct id) as ct
from user 
where age >18

年龄最大的在每个城市

select city ,MAX(age)
from user 
group by city

每个city的人数

select city ,count(id)
from user 
group by city

年龄段分层

使用分段函数，构造新的id18-29，然后再使用这个id来进行聚合

-- 年龄段分层
select 
CASE 
	WHEN age>=0 and age<18 then
		'0-17'
		WHEN age>=18 and age<29 then
		'18-29'
		WHEN age>=30 and age<59 then
		'30-29'
	ELSE
		'60+'
END as judge,
count(*)
from user 
group by judge

出现最多的

select city ,count(*) as t
from user 
group by city
ORDER BY t DESC
limit 1

订单表（单表）

有个订单表t order,有id、money、country:字段

查找country是中国的，money倒序的第100条到第200条

记录（顺丰）

select * 
from t_order 
where country="中国"
order by money DESC
limit 100 offset 99

使用offset自动移除前面的99个，然后限制查找100个

select * 
from A 
left join B on A.tid = B.tid 
where b.tid =null

时间函数使用

select vid,count(*) as c
from vedio 
where play_time > DATE_SUB(now(),INTERVAL 1 hour)
GROUP BY vid
order by C desc
limit 1