根据book_id,class_id确定老师uid
select user_id
from lessons
left join book on lessons.lesson_id = book.lesson_id
where lessons.class_id = 2
and book.id = 1
group by user_id;
根据book_id,class_id,semester_id确定老师uid
select user_id
from lessons
left join book on lessons.lesson_id = book.lesson_id
where lessons.class_id = 2
and book.id = 1 and
semester_id = 2
group by user_id;
分析:写sql要脑袋里考虑很多东西,合理性~根据条件,获取什么~从哪里获取~
题目:
user表
姓名 年龄 性别 出身日期
name age sex birth_day
a 20 0
b 21 1
c 21 0
d 23 1
e 20 1
写sql
年龄 男数 女数
SELECT age AS '年龄', username AS '姓名',SUM(CASE WHEN sex = '' THEN 1 ELSE 0 END) AS '男数',SUM(CASE WHEN sex='' THEN 1 ELSE 0 END) AS '女数' FROM userinfo GROUP BY age