SQL语句学习笔记

1.单个商品的规格数、最低价、最高价
SELECT a.good_id,a.good_name,count(b.model_id) number,min(price) min_price,max(price) max_price FROM tt_goods a LEFT JOIN tt_goods_model b ON a.good_id = b.good_id GROUP BY a.good_id

2.有3个规格的销售方式是拼团的商品
SELECT a.good_id,a.good_name,count(if(b.sale_type = 2,b.model_id,null)) as sale_type FROM tt_goods a LEFT JOIN tt_goods_model b ON a.good_id = b.good_id GROUP BY a.good_id having sale_type > 3

3.没有规格的商品(2张表的差集)
SELECT a.*,b.* FROM tt_goods a LEFT JOIN tt_goods_model b ON a.good_id = b.good_id where b.model_id is null

4.批量处理商品状态、售价
update tt_goods_model
set status = case 
when id = 252 then 1
when id = 253 then 2
END,
price = case 
when id = 252 then 200 
when id = 253 then 400
end 
where id in(252,253)

5.各年级成绩前5(每组前几条记录)
select * from student a where 5 > (select count(*) from student where ClassId = a.ClassId and Score > a.Score) order by ClassId asc,Score desc

6.查询选修了2门课程以上（含2门，但不含1号课程）,学生学号和选修课程数
select Sno,count(course.Cno) as total From SC,course Where course.Cno=SC.Cno and course.Cno !=1 Group by Sno Having Count(course.Cno)>=2

7.随机获取10条数据
SELECT * FROM table WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM table))) ORDER BY id LIMIT 0,10

8.根据不同状态统计出现的次数(case when 的使用)
select userid, sum(case when status =1 then 1 else 0 end) as '1',
sum(case when status =2 then 1 else 0 end) as '2',
sum(case when status =3 then 1 else 0 end) as '3'
from user group by userid;